• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Export from Access Querry to Excel

Experts:

I have a form with a button on it and I also have 3 Queries.  What I would like to have happen is—when the button is clicked, all 3 Queries get exported to 3 separate Excel Spreadsheets, as follows:

Querry1 goes to Spreadsheet1 located at C: /Matt/User/Desktop/Spreadsheet1
Querry2 goes to Spreadsheet2 C: /Matt/User/Desktop/Spreadsheet2
Querry3 goes to Spreadsheet3 C: /Matt/User/Desktop/Spreadsheet3

What I would like to happen is, when the button is clicked—each of these Queries get exported to their respective Spreadsheet.

Note:  I would like to overwrite any data within those spreadsheets every time an export is performed without receiving a Warning Dialog Box.

Also, once the export is complete, I would like the Access Database to display an 'Ok' Dialog Box that simply says "Report Has Been Exported"

Does anyone have a code I can place within my button?
0
mdstalla
Asked:
mdstalla
1 Solution
 
Eric ShermanAccountant/DeveloperCommented:
Try this ...

    Dim strFileName as String
    strFileName = "C: /Matt/User/Desktop/Spreadsheet1.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry1", strFileName, True
    strFileName = "C: /Matt/User/Desktop/Spreadsheet2"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry2", strFileName, True
    strFileName = "C: /Matt/User/Desktop/Spreadsheet3"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry3", strFileName, True
 
 MsgBox "Report Has Been Exported"


ET
0
 
mdstallaAuthor Commented:
ET:

The code is producing an error.

It has a problem with:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry1", strFileName, True

and

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry2", strFileName, True

I don't know if it makes a difference but I'm using Access and Excel 2013

and

The spreadsheets are all .xlsx
0
 
Gustav BrockCIOCommented:
Try with:

    Const cstrFileName As String = "C: /Matt/User/Desktop/Spreadsheet{0}.xlsx"
    Const cstrQuery As String = "Querry{0}"

    Dim strFileName As String
    Dim strQuery As String
    Dim intFile As Integer

    For intFile = 1 To 3
         strFileName = Replace(cstrFileName, "{0}", CStr(intFile))
         strQuery = Replace(cstrQuery, "{0}", CStr(intFile))
         DoCmd.OutputTo acOutputQuery, strQuery, acFormatXlsx, strFileName, False, , , acExportQualityPrint
    Next
    MsgBox "Exported three reports.", vbInformation, "Export"

/gustav
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Eric ShermanAccountant/DeveloperCommented:
What error are you getting??

ET
0
 
tomfarrarCommented:
Maybe "acSpreadsheetTypeExcel12"?
0
 
mdstallaAuthor Commented:
Your code worked great.  Thanks E!
0
 
Eric ShermanAccountant/DeveloperCommented:
Thanks for the points.

ET
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now