Exporting Query to Excel Template

[Number11]

Hi, I have a button on a form that i wish to export 4 queries into an Excel template, this first one works fine but the following 3 are all blank spreadsheets are created as expected just no data

here is my code..


Dim XL As Excel.Application
Set XL = CreateObject("excel.application")

Dim RST
Dim vFile

vFile = "Template Location"

Set RST = CurrentDb.OpenRecordset("Query")

With XL
.Visible = False
.Workbooks.Open vFile
.Sheets("Test").Select
.Range("A4").Select
.ActiveCell.CopyFromRecordset RST
.ActiveWorkbook.SaveAs filename:=("Output Location")
.ActiveWorkbook.Close
.Application.Quit
End With

Dim vFile
Dim RST1
vFile1 = "Template Location"

Set RST1 = CurrentDb.OpenRecordset("Query1")

With XL
.Visible = False
.Workbooks.Open vFile1
.Sheets("Test").Select
.Range("A4").Select
.ActiveCell.CopyFromRecordset RST
.ActiveWorkbook.SaveAs filename:=("Output Location ")
.ActiveWorkbook.Close
.Application.Quit
End With

and so on for the others

 

[vba_php]

number, this is *kind of* unrelated, but it's the same thing you are doing, just through the interface. are the 2 operations related perhaps?

https://www.access-programmers.co.uk/forums/threads/exportwithformatting-template-file-field.309227/

 

[Cronk]

This can't be the code you are trying to run because the duplicate declarations of some variables would give compile errors.

For a start, you open an instance of Excel at the beginning and then close it with

application.quit

in the first With/End with

 

[GinaWhipp]

Hmm, do any of these queries have parameters?