Trying to export a query to an existing excel template.

[bluenose83]

Hi Guys, really no idea about VBA code but I trying to learn.

I have a database which I am trying to set up to replace a number of existing excel documents. I do however need the database to transfer a query to an existing excel template.

So for example the database is called "test".
The query is called "testquery"
The excel document is called "testspreadsheet"
The Tab it needs to go to is called "template"
And I need it to start pasting into cell A2.

I have been looking at the transfer spreadsheet method but I my limited understanding of VBA is becoming a problem.

Any help or advice would be greatly appreciated.

 

[ranman256]

1. If you are exporting data to an existing xl file, existing tab,then you must NOTE:
you cannot use docmd.Transferspreadsheet,
if the data is smaller (fewer records) than it was in the previous export, then it will LEAVE the extra records. So now you have new and old records.
so
you must create an instance of Excel, take control of the workbook, erase that tab, THEN paste data from TRANSFERSPREADSHEET.

2.
If you need to export data into the tab in a given Cell, (other than A1) then you must do much the same as above:
you must create an instance of Excel, take control of the workbook, move to the cell, clear the cells, paste the data using COPYFROMRECORDSET.

 '///NOTE : YOU MUST ADD excel object library IN THE VBE menu,references)
Public Sub PostData2XL()
Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile

vFile = "c:\folder\testspreadsheet.xls"

Set rst = CurrentDb.OpenRecordset("testquery")
With XL
   .Visible = True
   .Workbooks.Open vFile
   .Sheets("template").Select
   .Range("A2").Select
   .ActiveCell.CopyFromRecordset rst
   .ActiveWorkbook.Save
End With
Set rst = Nothing
Set XL = Nothing
End Sub

 

[bluenose83]

Thanks for your reply.

I will give this a go now. Sorry should have said also, the information forwarded to a blank template, its then sent off to another team and my template is cleared ready for the next day if that makes sense.

 

[bluenose83]

Worked a treat, thank you very much!

 

[xenou]

Once its working you can do it "invisibly". Also as a rule I'd always close the workbook and quit the Excel app. Using on object variable for Excel will also prevent your code from failing if/when a new version of Excel is installed.

 '///NOTE : YOU MUST ADD excel object library IN THE VBE menu,references)
Public Sub PostData2XL()
Dim rst
Dim XL As Object 'Excel.Application
Dim wb As Object
Dim vFile

	vFile = "c:\folder\testspreadsheet.xls"

	'//Create Objects
	Set XL = CreateObject("excel.application")
	Set rst = CurrentDb.OpenRecordset("testquery")

	'//Write Data in Excel workbook
	set wb = XL.Workbooks.Open vFile
    wb.Sheets("template").Range("A2").CopyFromRecordset rst
    wb.Save
	
	'//Close objects
	wb.Close
	XL.Quit
	
	'//Cleanup object references (generally not necessary but good form)
	set wb = Nothing
	set rst = Nothing
	Set XL = Nothing

End Sub