Saving an Excel File from Access Automation...
 

I posted this before in the Access Newsgroups, but we never found the
solution...It works about half the time.

Here's a snippet from the Access code...

....BegPar and EndPar are unbound textboxes on an Access Form...
A = Month(BegPar): K = Day(BegPar): C = Year(BegPar)
L = Month(EndPar): E = Day(EndPar): F = Year(EndPar)
M = "AIP" & Right("0" & A, 2) & Right("0" & K, 2) _
& Right(C, 2) & "_" & Right("0" & L, 2) & Right("0" & E, 2) _
& Right(F, 2)
If IsNull(Combo505) Then
S = "_Both"
IPFile = "C:\BobDev\" & M & S & ".xls"
Else
S = Combo505 & "_"
IPFile = "C:\BobDev\" & S & M & ".xls"
End If
bIPFile = True: ChDir "C:\BobDev"
If Dir(IPFile) < "" Then Kill IPFile
AAA3:
ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

...When it fails...it's the code directly under "AAA3:" above.
The "ActiveWorkbook" is a File called thru Automation...after I
populate the Excel data from Access Automation, I save the File
w/ the "IPFile" string above.

Saving an Excel File from Access Automation...
 

Bob,
If this is coded in Access, you need the fully qualified reference to the
workbook.
Assuming you have a reference to Excel and have an instance:

ExcelApp.ActiveWorkbook.....

NickHK

"Bob Barnes" wrote in message
...
I posted this before in the Access Newsgroups, but we never found the
solution...It works about half the time.

Here's a snippet from the Access code...

...BegPar and EndPar are unbound textboxes on an Access Form...
A = Month(BegPar): K = Day(BegPar): C = Year(BegPar)
L = Month(EndPar): E = Day(EndPar): F = Year(EndPar)
M = "AIP" & Right("0" & A, 2) & Right("0" & K, 2) _
& Right(C, 2) & "_" & Right("0" & L, 2) & Right("0" & E, 2) _
& Right(F, 2)
If IsNull(Combo505) Then
S = "_Both"
IPFile = "C:\BobDev\" & M & S & ".xls"
Else
S = Combo505 & "_"
IPFile = "C:\BobDev\" & S & M & ".xls"
End If
bIPFile = True: ChDir "C:\BobDev"
If Dir(IPFile) < "" Then Kill IPFile
AAA3:
ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

..When it fails...it's the code directly under "AAA3:" above.
The "ActiveWorkbook" is a File called thru Automation...after I
populate the Excel data from Access Automation, I save the File
w/ the "IPFile" string above.

Saving an Excel File from Access Automation...
 

I'll test...It has to be either...

Set objXLApp = CreateObject("Excel.Application")

...or.. "M" is a string to a Filename "template'

Set objXLWb = objXLApp.Workbooks.Open(M)

Probably objXLApp ???

THANK you - Bob

"NickHK" wrote:

Bob,
If this is coded in Access, you need the fully qualified reference to the
workbook.
Assuming you have a reference to Excel and have an instance:

ExcelApp.ActiveWorkbook.....

NickHK

"Bob Barnes" wrote in message
...
I posted this before in the Access Newsgroups, but we never found the
solution...It works about half the time.

Here's a snippet from the Access code...

...BegPar and EndPar are unbound textboxes on an Access Form...
A = Month(BegPar): K = Day(BegPar): C = Year(BegPar)
L = Month(EndPar): E = Day(EndPar): F = Year(EndPar)
M = "AIP" & Right("0" & A, 2) & Right("0" & K, 2) _
& Right(C, 2) & "_" & Right("0" & L, 2) & Right("0" & E, 2) _
& Right(F, 2)
If IsNull(Combo505) Then
S = "_Both"
IPFile = "C:\BobDev\" & M & S & ".xls"
Else
S = Combo505 & "_"
IPFile = "C:\BobDev\" & S & M & ".xls"
End If
bIPFile = True: ChDir "C:\BobDev"
If Dir(IPFile) < "" Then Kill IPFile
AAA3:
ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

..When it fails...it's the code directly under "AAA3:" above.
The "ActiveWorkbook" is a File called thru Automation...after I
populate the Excel data from Access Automation, I save the File
w/ the "IPFile" string above.

Saving an Excel File from Access Automation...
 

Probably objXLApp

That was it..THANK you - Bob

"NickHK" wrote:

Bob,
If this is coded in Access, you need the fully qualified reference to the
workbook.
Assuming you have a reference to Excel and have an instance:

ExcelApp.ActiveWorkbook.....

NickHK

"Bob Barnes" wrote in message
...
I posted this before in the Access Newsgroups, but we never found the
solution...It works about half the time.

Here's a snippet from the Access code...

...BegPar and EndPar are unbound textboxes on an Access Form...
A = Month(BegPar): K = Day(BegPar): C = Year(BegPar)
L = Month(EndPar): E = Day(EndPar): F = Year(EndPar)
M = "AIP" & Right("0" & A, 2) & Right("0" & K, 2) _
& Right(C, 2) & "_" & Right("0" & L, 2) & Right("0" & E, 2) _
& Right(F, 2)
If IsNull(Combo505) Then
S = "_Both"
IPFile = "C:\BobDev\" & M & S & ".xls"
Else
S = Combo505 & "_"
IPFile = "C:\BobDev\" & S & M & ".xls"
End If
bIPFile = True: ChDir "C:\BobDev"
If Dir(IPFile) < "" Then Kill IPFile
AAA3:
ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

..When it fails...it's the code directly under "AAA3:" above.
The "ActiveWorkbook" is a File called thru Automation...after I
populate the Excel data from Access Automation, I save the File
w/ the "IPFile" string above.