Code Help: Pasting Query Contents to Spreadsheet

[bbatson]

Hello, I need a little help with some code. I am trying to export the contents of a query to an Excel spreadsheet. The code below will find a named cell as the starting point and paste contents. Right now, it's just set to "test" in a named cell called "TestCell". How would I go about pasting the contents of a query, call it "qry_test"?

Dim obj As Object
Set obj = GetObject("C:\file path")
obj.Application.Visible = True
obj.Windows(1).Visible = True
obj.Application.Goto "TestCell"
obj.Application.ActiveCell.FormulaR1C1 = "test"
End Sub

Thank you!

[wvmitchell]

Three thoughts. Excuse me if this is too obvious or you don't like the way the data appears in Excel...

First, if you are viewing the query on screen, from the Access menu bar you can click Tools ~ Office Links ~ Analyze it with Microsoft Excel, and Access will create an Excel file that matches your query & place it in your default directory, usually My Documents.

Second, it you already have a pre-built Excel file with formatting, you can open Excel and create a link from Excel to Access - from the Excel menu bar click

Data ~ Import External Data ~ New Database Query, answer the seemingly endless stream of questions :-) Haven't done this with Excel for quite some time, but here is a link that describes how to connect from Excel to SQL Server, maybe that will help:

Create SQL Server database query with MS Excel

and then place your data to begin at the desired spot. You will have the option to refresh the data when you open Excel, or you can make it refresh on demand. This will preserve most of the formatting.

Lastly, if this is too much effort, simpy view your query on screen, press Ctrl-A to select all rows & all data, then open a new Excel workbook & paste the data in there. (However, this option will not work if your query is a SQL pass-thru, you'll get 'operation on this type of object is not supported')

In any othese example, remember that Excel 2003 can accept 65,635 rows of data but Excel 2007 can handle just over a million rows.

[QVeen72]

Hi,

In Access, Design a Macro which will transfer the contents..
Create New Macro Action = "TransferSpreadSheet"
Below Properties Select :
TransferType =Export
Spread Sheet Type : Excel 5-7
TableName : Query1
FileName : C:\MyExcel
HasFields = Yes

and Save the Macro. Just Run this Macro, and the datas will be exported..

Regards
Veena

[jarekz]

I think the best way to export the contents of a query to an Excel spreadsheet is by using DoCmd.Transferspreadsheet in your VBA code. It is better than doing it by macro, because in the code you can choose whatever path and filename you wish to export the data to. In Macro the filename and path is always the same and can't be changed.

But I don't know if it is possible to export the data to a specific cell in an Excel spreadsheet. I suggest you read MS Access Help on DoCmd.Transferspreadsheet. Good luck!

Jarek