Solved

Access export to excel with VBA

Posted on 2013-11-20
4
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

726 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