I am new to the whole DB world and I have been trying to get my Access DB to export specific data from a query to an existing Excel Form. I need to run a query to have the data exported from my access query and placed into specific cells in an exsisting excel spreadsheet.
How do I do this, I have tried the export macro but that just replaces the whole spreadsheet.
Please help. Also, Like I said I am new to this..so if you have crayons...please use them to explain.
You'll have to use automation to do this (ie. instantiate Excel in Access and control the instance of Excel from your Access Application).
To do so, you first have to create a reference to the Microsoft Excel 11.0 Object Library (11.0 is for Office 2003, it can chage according to the version of Office).
To create a reference, open a module, in the Tools menu select References, check the desired reference into the list and click OK.
Here is a short example explaining how it works:
Dim AppXL As Excel.Application
Dim BookXL As Excel.Workbook
Dim FilePath As String
Set AppXL = New Excel.Application
FilePath = "c:\myfile.xls"
' do whatever you want to do with the selected sheet here
Set AppXL = Nothing
One thing that I do and I find quite useful is the CopyFromRecordset method. This allows you to load a recordset with the data you want then paste it (unformatted) into a specific cell on the worksheet.
It's a fast way to dump data into a spreadsheet. If need be then you can have code go back and do the formatting for you.