VBA to Export Access Select Queries to Excel Files

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA to Export Access Select Queries to Excel Files

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Location
    UK
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    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
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    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
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Many thanks for your guidance Joe4.

  6. #6
    Board Regular
    Join Date
    May 2011
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks Joe4...

    With some variance, this helps me too...

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Any cure ?

  10. #10
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,009
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

      
    Look at this link

    Excel Tip: Export More Than 65000 Lines From MS Access to MS Excel

    Also, make sure you are exporting to an .xlsx file.
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



User Tag List

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