VBA to Export Access Select Queries to Excel Files

brendanolear

Active Member
Joined
Apr 26, 2008
Messages
366
I am working in Access 2010.

I have some 20 Select queries in my Access DB. Queries are named GBQuery01, GBQuery02, GBQuery03 etc.

I wish to be be able to use VBA to run GBQuery01, then export the result to an Excel 2010 file, this would be a newly created file titled GBQuery01.xlsx.

I would then wish to move on to GBQuery02, run, export etc across all 20 queries.

The end result would be 20 newly created xlsx files.

Can anyone assist with a VBA coding to meet this requirement.

Thanks in advance.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Create a macro that exports your first query to Excel (using the TransferSpreadsheet command). Then, from the Macros ribbon, select "Convert Macros to Visual Basic". This will give you the VBA code equivalent of that macro. You can use that as the "building blocks" for your VBA code.

If you post that code here, we can help you write the loop you need to loop through all 20 queries and export them all.
 
Upvote 0
Thanks Joe4.

The basic transfer on the initial query is as follows:

Private Sub ExportExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "GBQuery01", "C:\Users\Gary\Documents\GBQuery01.xlsx", True
End Sub

The loop or sequential capture thereafter is where I am having difficulty.
 
Upvote 0
This should do it:
Code:
Private Sub ExportExcel()
 
    Dim i As Integer
    Dim myQueryName As String
    Dim myExportFileName As String
 
    For i = 1 To 20
        myQueryName = "GBQuery" & Format(i, "00")
        myExportFileName = "C:\Users\Gary\Documents\GBQuery" & Format(i, "00") & ".xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myQueryName, myExportFileName, True
    Next i
 
End Sub
 
Upvote 0
This should do it:
Code:
Private Sub ExportExcel()
 
    Dim i As Integer
    Dim myQueryName As String
    Dim myExportFileName As String
 
    For i = 1 To 20
        myQueryName = "GBQuery" & Format(i, "00")
        myExportFileName = "C:\Users\Gary\Documents\GBQuery" & Format(i, "00") & ".xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myQueryName, myExportFileName, True
    Next i
 
End Sub

This is fantastic!

I'm hoping someone could elaborate on this with the following differences:

1) There are 46 queries, formatted so that they are respectively numbered 01 to 46, followed by a space and their given names.
2) I would need to have these queries run and all results pasted (or amended) to a single sheet or Recordset.
3) Export the Recordset to Excel and save with a batch/date number and .xlsx to a specified location.

I'm stuck mostly on (2). Items (1) (a variation of the above) and (3) I have the code for. I'm new to VBA, so any guidance would be greatly appreciated :) please and thank you greatly.

oc
 
Upvote 0
Works fine - but although I got Office 13 - Access only supports Excell 97 - meaning i can only get 65ooo lines out.

Any cure ?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top