Access VB - Open Excel, Save as Xls

[dvent]

Hi All,

I need a few lines to add to a script to open a workbook and save it again in *.xls format. It is currently in *.xlsx format and my macro cannot open it for some reason.

The file is declared already as a full path as "excelPath" e.g. C:/Desktop/File.xlsx

Can anyone assist?

Thanks
dvent

 

[ajetrumpet]

the best thing maybe to do here is to open up excel file, start recording a macro, then save the file as a 97-2003 file using mouse clicks and the interface. see what code it generates, and get an idea from that.

 

[Endre]

To get you started:

Open up your VBA Code in MSAccess where you wish to store your script excel code.

In the Access VBA menu options choose Tools > References.
Select Microsoft Excel nn.n Object Library.

(nn.n is latest version e.g. 11.0)


Now create an object of type Excel Workbook in your script:

Dim xl_Book1 As Excel.Workbook
Set xl_Book1 = Excel.Workbooks.Open("C:\MyFiles\002 Book.xls")
etc...

Now you can start using xl_Book1 in your VBA code..........
To get better help, go to Excel VBA Help.....

 

[boblarson]

Hi All,

I need a few lines to add to a script to open a workbook and save it again in *.xls format. It is currently in *.xlsx format and my macro cannot open it for some reason.

The file is declared already as a full path as "excelPath" e.g. C:/Desktop/File.xlsx

Can anyone assist?

Thanks
dvent

What version of Access are you using? Versions prior to 2007 will not have a way to open/save from/to xlsx format.