Open Excel File from Access ---then SaveAs???

[MrsTFB]

Hey,
I need a macro or code in Access that will open a particular file in Excel, then do the SaveAs command.

Is that possible? I'm thinking I can at least get to the Excel file, then I can create a VBA macro in the Excel to Save As...easier?
Also, the text to name the new file, is in the Access table...hehe!
What do y'all think? INput?
Thanks,
MrsTFB in Tennessee

 

[Tigerlili3]

Hi! You could accomplish what you are asking by creating an Excel Macro as follows:

Sub SaveExcelFile()
Workbooks.Open "C:\Path to the workbook to which you would like to apply the Save As process\filename.xls"
ActiveWorkbook.SaveAs "C:\Path to which you would like to save the file listed above\filename.xls", FileFormat:=xlNormal
ActiveWorkbook.Close
End Sub

Then you could call the Excel macro above from Access by creating a VBA module containing the following code:

Public Function NameHere()
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "C:\Path to the Workbook that contains your Excel macros\filename.xls"
XL.Run "SaveExcelFile"
XL.Workbooks.Close
XL.Application.Quit
Set XL = Nothing
End Function

I hope this helps!