Results 1 to 8 of 8

VBA to Export Access Select Queries to Excel Files

This is a discussion on VBA to Export Access Select Queries to Excel Files within the Microsoft Access forums, part of the Question Forums category; I am working in Access 2010. I have some 20 Select queries in my Access DB. Queries are named GBQuery01, ...

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Location
    UK
    Posts
    346

    Default VBA to Export Access Select Queries to Excel Files

    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.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,278

    Default Re: VBA to Export Access Select Queries to Excel Files

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2008
    Location
    UK
    Posts
    346

    Default Re: VBA to Export Access Select Queries to Excel Files

    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.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,278

    Default Re: VBA to Export Access Select Queries to Excel Files

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Apr 2008
    Location
    UK
    Posts
    346

    Default Re: VBA to Export Access Select Queries to Excel Files

    Many thanks for your guidance Joe4.

  6. #6
    ISW
    ISW is offline
    Board Regular
    Join Date
    May 2011
    Posts
    78

    Default Re: VBA to Export Access Select Queries to Excel Files

    Great one Joe. Wish I had this a few weeks ago.

  7. #7
    New Member
    Join Date
    Jun 2013
    Posts
    1

    Question Re: VBA to Export Access Select Queries to Excel Files

    Quote Originally Posted by Joe4 View Post
    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

  8. #8
    New Member
    Join Date
    Apr 2011
    Location
    Jakarta
    Posts
    3

    Default Re: VBA to Export Access Select Queries to Excel Files

    Thanks Joe4...

    With some variance, this helps me too...

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com