Solved: append query results to an existing .XLS sheet

[ironj32]

My goal is to use VBA to attach results from a select query in Access into an existing Excel Spreadsheet. I'd also like to specify where to begin pasting in the Excel Spreadsheet (like begining the paste at A4).

Thanks in advance for any assistance you can provide me!

Also, I am familiar with the "DoCmd.OutputTo acOutputQuery...", but I'd like to use an existing worksheet that is properly formatted for my needs.

[Marcster]

My thoughts on this would be to create a Recordset (either DAO or ADO), that way can use Excel's:

Sheets("Sheet1").Range("A4").CopyFromRecordset

[ironj32]

Marcster, would your suggestion require the action to be initiated from Excel? I would prefer for everything to be done in Access.

[Marcster]

It's possible in either Excel or Access.
I'll write an Access sub and post it soon...

[Marcster]

Something like this:


Sub ExportTableOrQueryToExcel()

Const strTableOrQuery = "tblBookReviews"

' define the path to the output file
Dim strPath As String
strPath = "C:\Example.xls"

' create and open an Excel workbook
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
objXL.Workbooks.Open (strPath)

' open the database
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim objField As DAO.Field
Set objDB = CurrentDb

' open the query/table
Dim strSQL As String
strSQL = "SELECT * FROM [" & strTableOrQuery & "]"
Set objRS = objDB.OpenRecordset(strSQL)


objXL.Workbooks.Item("example.xls").sheets("Sheet1").range("A4").copyfromrecordset objRS

objXL.Visible = True
Set objRS = Nothing
Set objXL = Nothing

End Sub

[CreganTur]

Marcster's ecample is almost exactly like a procedure I use to do the same thing- push the records in a recordset to Excel.

It is also a very good example of Late Binding.

Late Binding, if you're unaware, is when you Dim object variables as type Object. Then you can Set them to be a generic type of object, such as "Excel.Application". This is great when you want to automate Excel, but may not know what version of Excel your user is running.

The opposite of Late Binding is Early Binding. An example would be creating a reference to a specific version of Excel, and creating all of your objects specific for your reference. This is great when you know for a fact that all of your users are running Excel 2003, for example, but if a user is running a different version, they could run into errors.

HTH:thumb

[ironj32]

Thanks for you help! It almost works perfectly. However, it is only pasting the first row into the spreadsheet. I then get the following error in Access:

"Method 'CopyFromRecordset' of Object 'Range' failed"

When I Debug it highlights this row
objXL.Workbooks.Item("qryGenerateTestMatrix.xls").sheets("qryGenerateTestMatrix").range("A2").copyfromrecordset objRS

Any ideas?

Here is the code I am using
Sub ExportToExcel()

Const strTableOrQuery = "qryGenerateTestMatrix"

' define the path to the output file
Dim strPath As String
strPath = "D:\Documents and Settings\jlfogel\Desktop\Archer\Database\Generated Test Matrix's\qryGenerateTestMatrix.xls"

' create and open an Excel workbook
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
objXL.Workbooks.Open (strPath)

' open the database
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim objField As DAO.Field
Set objDB = CurrentDb

' open the query/table
Dim strSQL As String
strSQL = "SELECT * FROM [" & strTableOrQuery & "]"
Set objRS = objDB.OpenRecordset(strSQL)


objXL.Workbooks.Item("qryGenerateTestMatrix.xls").sheets("qryGenerateTestMatrix").range("A2").copyfromrecordset objRS

objXL.Visible = True
Set objRS = Nothing
Set objXL = Nothing

End Sub

[Marcster]

As it copies across the first row, there might be a problem with the second row.

Does this row contain a string of length > 255 characters?
Is it possible that the recordset contains > 65536 records?
If the Recordset object contains fields with OLE objects in them, this method fails.

[ironj32]

Yes, some of the fields contain over 255 characters. Unfortunately, I can't really shorten them up at all for our needs. Is this what's causing the error?

[Marcster]

Yes, afraid so.
Excel has a limit of 255.
From Excel help:
Column width255 characters
Type in 'Excel specifications and limits' in Excel help.

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

This limit exists in Excel 2007 too.