VBA save Excel file as .xls from Access

Question

Hi, I have to perform Import from Excel file into Access DB, without duplicates. Only way that this can be done is by creating table where you can Import Excel data, and then append It to destination table by buiilding a Query. Problem is that this Excel file has to be .xls format, none other format is working for me (don't know why). How can I do that from Access - desirable without opening Excel workbook, just change It's extension file name ? This is what I tried (I receive error : " object doesn't support this property or method")

Dim XFile As Excel.Application


Set XFile = CreateObject("Excel.Application")
XFile.Workbooks.Open "C:\Users\Mike\Desktop\Copy.xlsx", True

XcelFile.SaveAs Filename:="C:\Users\Mike\Desktop\Copy.xlsx", FileFormat:=56
XFile.Workbooks.Close

End Sub

As you see, I need to save from .xlsx, It's default format for Excel 2013.

Any help much appreciated !!

Your 'saveAs' file is the file you just opened. You should at least change the destination name, otherwise you will definitely face a conflict. — Philippe Grondier, Feb 27 '16 at 23:04
See [this answer](http://stackoverflow.com/a/5729426/4425957). — Porcupine911, Feb 27 '16 at 23:31
Change the extension from ".xlsx" to ".xls" in the "SaveAs" statement — user3598756, Feb 28 '16 at 00:25
@user3598756, I tried this and I'm still receiving same error. — LuckyLuke82, Feb 28 '16 at 18:17
@PhilippeGrondier, I changed destination & changed to ".xls" in SaveAs statement, but I receive same error. — LuckyLuke82, Feb 28 '16 at 18:19
Referencing the answer I linked to above, you could implement the same solution there and use `wb.SaveAs Filename:= "your filename", FileFormat:=56`. You can remove the lines that deal with "ws". — Porcupine911, Feb 28 '16 at 18:25

score 1 · Accepted Answer · answered Feb 28 '16 at 18:53

Thanks @Porcupine911, that worked, even without opening workbook :)

Dim XcelFile As Excel.Application
Dim wb As Excel.Workbook

Set XcelFile = New Excel.Application

Set wb = XcelFile.Workbooks.Open("Filepath here")

wb.SaveAs Filename:="Filename here, with different destination and desirable extension", FileFormat:=56
wb.Close
Set XcelFile = Nothing

End Sub

VBA save Excel file as .xls from Access

1 Answers1