Copy query to an excel template

[papasmurfuo9]

Hi

i have an excel spreadsheet in my documents (H:\My Documents\breakdown.xls)

i have a query called Qry_Breakdown, a form with a button named "Update Breakdown"

How do i get the details created in the query, to appear in my breakdown sheet, starting from "A2" as i have several headers matching the query


thanks in advance

 

[pr2-eugin]

Have you had a chance to look into DoCmd.TransferSpreadsheet function? But if you want to export only the data then you might be needing to open the excel file through Access (plenty available on the internet). Then use CopyRecordset function.

 

[papasmurfuo9]

Hi thanks for reply
but yes i need to open excel using access, i have been looking on the internet for something to copy, but cant seem to make it work
thanks

 

[pr2-eugin]

This should be something like,

Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset

Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "C:\FolderName\FileName.xlsx", True, False
XLApp.Visible = True

Set XLSheet = XLApp.Workbooks.Sheets("yourSheetName")
Set tmpRS = CurrentDb.OpenRecordset("yourQueryName")

XLSheet.Range("A2").CopyFromRecordset tmpRS

XLApp.Workbooks.Save
XLApp.Quit
tmpRS.Close

Set tmpRS = Nothing
set XLApp = Nothing

The above code needs a Reference to the Microsoft Excel library !

 

[papasmurfuo9]

thank you, where in this would i put the file path name to my excel workbook

thanks

 

[pr2-eugin]

Oops, sorry I missed it, I have made changes to the code, check it out.

 

[papasmurfuo9]

thanks i get a

compile error : method or data not found on

Set XLSheet = XLApp.Workbooks.Sheets("Master")

 

[pr2-eugin]

Okay how about this?

Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset

Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "C:\FolderName\FileName.xlsx", True, False
XLApp.Visible = True

Set XLSheet = [COLOR=Red][B]XLApp.Worksheets("yourSheetName")[/B][/COLOR]
Set tmpRS = CurrentDb.OpenRecordset("yourQueryName")

XLSheet.Range("A2").CopyFromRecordset tmpRS

XLApp.Workbooks.Save
XLApp.Quit
tmpRS.Close

Set tmpRS = Nothing
set XLApp = Nothing

Sorry if it is taking a bit long winded. I am only air coding and am not with a system to test it out !

 

[papasmurfuo9]

works, thank you kind sir

 

[pr2-eugin]

Glad you have it sorted ! Good luck.