Solved

Access export to excel with VBA

Posted on 2013-11-20
4
451 Views
Last Modified: 2013-11-20
Hello, I want to use the following code to export a query to excel.

With the code I do not get an error, but the file is not created.

Additionally, it would be great if the path where I save the excel file is the current users desktop.

Private Sub Command98_Click()
Dim dbs As DAO.Database
 Dim qdfTemp As DAO.QueryDef
 Dim strSQL As String, strQDF As String
 Set dbs = CurrentDb

 strSQL = "SELECT [DMR Tracking].[DMR#], [DMR Tracking].[DATE ISSUED], [DMR Tracking].[HOLD/SORT], [DMR Tracking].SCRAP," _
 & "[DMR Tracking].RTV, [DMR Tracking].[RMA FROM SUPPLIER], [DMR Tracking].[COMMENT 1], [DMR Tracking].[COMMENT 2]," _
 & "[DMR Tracking].[DMR DATE CLOSED], [DMR Tracking].VOID, [DMR Tracking].ShipperId FROM [DMR Tracking];"

strQDF = "_TempQuery_"
 Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
 qdfTemp.Close
 Set qdfTemp = Nothing

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
       strQDF, "C:\MyFileName.xls"

dbs.QueryDefs.Delete strQDF
 dbs.Close
 Set dbs = Nothing

End Sub

Open in new window

0
Comment
Question by:gogetsome
  • 2
  • 2
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39663278
Give this a try:

Private Sub Command98_Click()
Dim dbs As DAO.Database
 Dim qdfTemp As DAO.QueryDef
 Dim strSQL As String, strQDF As String
 Set dbs = CurrentDb

 strSQL = "SELECT [DMR Tracking].[DMR#], [DMR Tracking].[DATE ISSUED], [DMR Tracking].[HOLD/SORT], [DMR Tracking].SCRAP," _
 & "[DMR Tracking].RTV, [DMR Tracking].[RMA FROM SUPPLIER], [DMR Tracking].[COMMENT 1], [DMR Tracking].[COMMENT 2]," _
 & "[DMR Tracking].[DMR DATE CLOSED], [DMR Tracking].VOID, [DMR Tracking].ShipperId FROM [DMR Tracking];"

strQDF = "_TempQuery_"
 Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
 qdfTemp.Close
 Set qdfTemp = Nothing

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
       qdfTemp.Name , "C:\MyFileName.xls"

dbs.QueryDefs.Delete qdfTemp 
 dbs.Close
 Set dbs = Nothing

End Sub

Open in new window

0
 

Author Comment

by:gogetsome
ID: 39663308
Thank you once again for helping!

I'm getting a compile error: Type mismatch on this line:

dbs.QueryDefs.Delete qdfTemp
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39663364
Actually, your original code seems to work for me.  Try a folder other than C:\.  

This will write it to the desktop:
Dim dbs As DAO.Database
 Dim qdfTemp As DAO.QueryDef
 Dim strSQL As String, strQDF As String
 
 Dim objWSH As Object
 Set objWSH = CreateObject("WScript.Shell")
     
     'On Error GoTo ErrorHandler
     ' Create a shell object
 Set dbs = CurrentDb

 strSQL = "SELECT [DMR Tracking].[DMR#], [DMR Tracking].[DATE ISSUED], [DMR Tracking].[HOLD/SORT], [DMR Tracking].SCRAP," _
 & "[DMR Tracking].RTV, [DMR Tracking].[RMA FROM SUPPLIER], [DMR Tracking].[COMMENT 1], [DMR Tracking].[COMMENT 2]," _
 & "[DMR Tracking].[DMR DATE CLOSED], [DMR Tracking].VOID, [DMR Tracking].ShipperId FROM [DMR Tracking];"


strQDF = "TempQuery"
 Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)


 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
       strQDF, objWSH.SpecialFolders("Desktop") & "\MyFileName.xls", True


 qdfTemp.Close
 Set qdfTemp = Nothing
set objWSH = nothing
dbs.QueryDefs.Delete strQDF

 dbs.Close
 Set dbs = Nothing

End Function

Open in new window

0
 

Author Closing Comment

by:gogetsome
ID: 39663414
Excellent! Thank you so much!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question