Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation problem saving Excel as HTML. | Excel Programming | |||
Access VBA: Problem at saving an Excel file | Excel Programming | |||
Automation between Access and Excel | Excel Programming | |||
Access Automation to Excel | Excel Programming | |||
Automation Excel & Access | Excel Programming |