Export query to excel with Access 2007 VBA

[jdashm]

I am using a unbound form for the user to enter date ranges. These fields are tied to underlying query.
I want to export the query from access 2007 to excel 2007 when I click the command button in the form.


I have tried several attempts but not getting success.
The name of the query is "qryExcelDeliveryExport"

I thought it would just be an OnClick event that included the docmd.trtansferspreadshee acExport code.
Thanks in advance for looking at this.

[pbaldy]

"Not getting success" isn't very helpful. What actual code are you trying, and what exactly is going wrong? If an error, what's the description? Either TransferSpreadsheet or OutputTo should work.

[jdashm]

Here are two versions I have tried.... I have gotten errors that say I have not enough arguments for the docmd and others that say object not found. I should also say that my end result should be a new excel worksheet saved in that user's documents folder

Private Sub cmdExportXL_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExcelDeliveryExport", _
"C:\Users\Win7_Toshiba\Desktop\SIPS DB Project\2007_SIPS FullFillment DB_Users\Delivery_Information.xlsx", True

End Sub


Private Sub cmdExportXL_Click()
'DoCmd.OpenQuery "qryExcelDeliveryExport", acViewNormal
'Me.Visible = False
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("qryExcelDeliveryExport")


If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rst, _
"C:\Users\Win7_Toshiba\Desktop\SIPS DB Project\2007_SIPS FullFillment DB_Users\Delivery_Information.xlsx", True
rst.Close
Set rst = Nothing
End Sub

[aytee111]

First, make sure that the query is working - open the form, enter the dates, then instead of hitting the button to export, open the query.

Once you have determined that, then you can move to the code. Typing the command as in the first code you have above will show you the parameters for the command, make sure you have them all in the correct order.

Excel type 9 may be an "xls".

[pbaldy]

I would also try it all on one line until you have it working, then try to add line-continuation.

[jdashm]

Thanks for the reply.
I tested the query as you suggested without any issues and changed the extension of the excel file to .xls (Excel type 9)
when I run procedure I get the following error...

Run-Time error 3191
"Can't define field more than once"

[aytee111]

That sounds like it is an error in the query - ? I also found this error:
"dBASE has a 10-character limit for the size of field names. When the first 10 characters of two (or more) fields in a Microsoft Access table are identical, trying to export the table or query results in duplicate field names."
One wonders if the same sort of problem is occurring with Excel? Are some of your field names quite long and are hence being truncated?

Try to export the query manually - right click on the query and select Export.

[jdashm]

Thanks for the response..

I did review the field names and they seem to be distinct enough within the first 8 characters.
However, you gave me this idea... what I did change was not using the asterisk (*) to designate returning all fields from one of the tables in the query and replaced them with the individual fields. THAT DID IT!! YEA!

I do have three questions surrounding completing this task...
1) within the code how can I get it to export to a new excel workbook each time
2) I have some time fields that are exporting as date (i.e. 12:10PM is exporting as 1/12/1900). Is there a way in the code to retain Access formatting?
3) is there a way within the same code area to have excel launch and open the file automatically?

Many thanks.

[pbaldy]

1) You'd probably have to give it a unique name, or delete the existing file first.
2) One option would be to use the Format() function in the query. That creates a string value, which hopefully the export will leave alone.
3) Look at FollowHyperlink

[aytee111]

1) When I want to rename the worksheet I make a copy of the query immediately prior to the export, calling it the name I want for the worksheet. In your case you may want to add a date to make it unique. I then delete the query after the export.

[jdashm]

Thanks a lot... the Format fx worked.

[pbaldy]

No problem.