Solved

Access export to excel with VBA

Posted on 2013-11-20
4
440 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now