Hi everybody,
I've looked high and low for a nice definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.
After a lot of fiddling and searching the internet, i've put this together (by splicing other people's code). Shockingly, it doesn't work.
I've tried to remove any confidential info from the code below so it's not exactly the same.
The error seems to be with the SQL statement although that may just be the first error that it got to. I read that you can't refer to a Query if it has a criteria and that you have to write the SQL directly into the code. Is anybody able to make some adjustments to get this code to work? Or if i'm way off base, is there somewhere I can find the code to do this?
I had a similar issue not so long ago when I was exporting to Word which the people on this forum were extremely helpful with. I had hoped this would be easier.
I've looked high and low for a nice definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.
After a lot of fiddling and searching the internet, i've put this together (by splicing other people's code). Shockingly, it doesn't work.
I've tried to remove any confidential info from the code below so it's not exactly the same.
Code:
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
Dim strFolder as String
strFolder = ("C:\Profiles\"& [Name] & "\")
Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "C:\Profiles\Template.xlsx", True, False
XLApp.Visible = False
Set XLSheet = XLApp.Worksheets("Sheet1")
Set tmpRS = "SELECT * FROM tblCustomers WHERE tblCustomers.ID = " & Me.ID & ";"
XLSheet.Range("A2").CopyFromRecordset tmpRS
XLAPP.SaveAs (strFolder & "Name.xlsx")
XLAPP.Close wdDoNotSaveChanges
tmpRS.Close
Set tmpRS = Nothing
set XLApp = Nothing
End Sub
I had a similar issue not so long ago when I was exporting to Word which the people on this forum were extremely helpful with. I had hoped this would be easier.