Exporting to Excel template and saving as a new file (1 Viewer)

[CrisMonty]

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.

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

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.

 

[GinaWhipp]

Becuase you saving Excel and not the Workbook, try...

Dim xlWBk As Object
 
Set xlWBk = XLApp.Workbooks.Open(strFolder & "Name.xls")
 
xlWBk.SaveAs strFolder & "Name.xlsx", 51

Probably needs a little tweaking to fit you scenario.

 

[CrisMonty]

I'm still getting stuck on the SQL. I was wondering if it would maybe be easier to make the code first open the template and save it as the new file and then do transferspreadsheet to the new file.

 

[GinaWhipp]

You could but I use the above all the time. Perhpas you should post what you have now and what's it's doing so we can tweak.

 

[CrisMonty]

I'll try to knock together a sample db and post it here. The problem is, i have access to Access at work but no internet and at home I have the Internet but no Access.

 

[GinaWhipp]

Sorry, I meant post the code you have now, no need to post the database!

 

[CrisMonty]

okay, here's my actual code so far.

Private Sub cmdExport_Click()
 
Dim Sheet As Object
Dim strFolder As String
Dim Password As String
Dim fso As Object
 
Set fso = CreateObject("Scripting.FileSystemObject")
 
If Me.includePassword = True Then
    Password = InputBox("Password", "Create Password")
End If
 
If Me.[Prize amount] > 20000 Then
    strFolder = ("Z:\General\THL\Admin Folder - WIP\High Prize Winners\Winner Files\100K\" & [Forename] & " " & [Surname] & "\")
Else
    strFolder = ("Z:\General\THL\Admin Folder - WIP\High Prize Winners\Winner Files\10K\" & [Forename] & " " & [Surname] & "\")
End If
 
If Not fso.FolderExists(strFolder) Then
fso.CreateFolder (strFolder)
End If
 
Dim X As Object
Dim Y As Object
Dim XL As Object
Dim rs As Recordset
Dim MyQueryDef As Object
Set X = CreateObject("Excel.Application")
Set Y = X.Workbooks.Open("Z:\General\THL\Admin Folder - WIP\High Prize Winners\High Prize Docs\PRTemplate.xlsx")
Set XL = Y.Sheets("PR Details")
 
Set rs = CurrentDb.OpenRecordset("SELECT [Title], [Forename], [Surname], [Draw Date], [Claim Status], [Prize Amount], [Address Line 1], [Address Line 2], [Address Line 3], [Post Code], [DOB], [Telephone], [PR Notes], [PR Level], [PR Call Date]," & _
"From [tblWinners]" & _
"Where ((([tblWinners].[Post Code]) = [Forms]![FrmAllWinners]![Post Code]))")
 
XL.Range("A2").CopyFromRecordset rs
 
 
 
Y.SaveAs "Z:\General\THL\Admin Folder - WIP\High Prize Winners\High Prize Docs\" & [Surname] & ".xls"
 
X.Visible = True
Set X = Nothing
Set Y = Nothing
Set XL = Nothing
rs.Close
Set rs = Nothing
End Sub

the error occurs with the SQL (although there may be errors after that.)

(the select statement contains a reserved word or an argument name that is mispelled or missing)

My database has a form with all of a single winners info on it. When Export Pr is pressed, it opens a window with the name, a button to add a password and the cmdExport button.

This form is based on a query that just selects the current record in the previous form (postcode = forms!frmallwinners!postcode)

When the button is pressed, i want it to export the file to excel as "the customer's name - Pr" (and password protect it if the button is pressed.)

 

[GinaWhipp]

First problem, using parameter queries. So, I am gathering you want people to be able to export based on the Postal Code? Before I alter this slightly, are there going to be any other parameters?

 

[CrisMonty]

No other parameters. I actually just want to export the current record on the form if that makes it easier.

 

[GinaWhipp]

Umm, this...

Where ((([tblWinners].[Post Code]) = [Forms]![FrmAllWinners]![Post Code]))")

...is a parameter. Using the Postal Code to export the current record is kind of strange. Why not the Primary Key? Suppose more then record has the same Postal Code?

 

[CrisMonty]

Yeah, i was being a bit lazy there. There is an ID field but I didn't want that to export. It's a throwback to when I was using transfer spreadsheet. Yeah, i will use the ID instead. I know i can't just refer directly to a query with parameters but can it be done with parameters with SQL?

 

[GinaWhipp]

Hmm, lazy not allowed . You need to do something like...

WHERE (((tblProductTypes.ptProductTypeID) = " & Me.cboProductTypeID & "))"

OR you might (never tried this) be able to do something like...

([tblWinners].[Post Code]) = " & [Forms]![FrmAllWinners]![Post Code] & "))"

Hmm, those parathesis might not be right. Think I gave you enough to go on or should I type it all out?

 

[CrisMonty]

Fantastic, I'll try this when I get back to work. Thank you for your help.

 

[GinaWhipp]

No problem, I'll be here!