ms Access export to excel

[sparkbyte]

I have a menu button that creates a table with all UPS tracking numbers parsed so that an export can be created based on the shipping method.

Here is the code I have been trying to get running.
The SQL querie works in SQL manager.

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strFileName As String

strSQL1 = "SELECT BoxNumber, FileNumber, TrackingDate, TrackingNumberPrefix, TrackingNumberAct, TrackingNumberShipping, TrackingNumberParsel"
strSQL2 = "FROM dbo.tblTrackingParse"
strSQL3 = "WHERE FileNumber <> '.box.end.') AND (BoxNumber NOT LIKE 'NBC%') AND (LEN(BoxNumber) < '30') AND (TrackingNumberPrefix LIKE '1Z')"
strSQL = strSQL1 & " " & strSQL2 & " " & strSQL3

'Debug.Print strSQL

strFileName = "c:\test.xls"

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL, strFileName, True, "Shipping Report"

Thanks

John Fuhrman

 

[sparkbyte]

Sorry, the debugger highlights this row.

Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)


Thanks

John Fuhrman

 

[henniec]

Hi,
With all the problems you had with this code, can you create a query that would give you what you need?

I cant remember what solutions I found regarding your last "error 91: Object variable or With block variable not set". I found in many cases its far easier to create a make table query and use the table to transfer to Excel using the method PWise suggested.

The make table query works always when I receive the error message "needs on or more parameters". It may help here too.

Hennie