[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export queries to multiple excel worksheets in the same workbook using VBA.

Posted on 2006-11-21
2
Medium Priority
?
987 Views
Last Modified: 2008-02-01
Hi,

I would like to export a number of access queries to different worksheets in the same workbook using Access VBA.  What's the best method?



0
Comment
Question by:geraintcollins
2 Comments
 
LVL 34

Accepted Solution

by:
jefftwilley earned 200 total points
ID: 17990274
Create a table to put your query names into and use this.

Function OutToExcel()
Dim rsOUT As DAO.Recordset
Dim strOutPutFile As String
Dim strSpecTable As String
Dim strLoc As String
Dim strCo As String

    strOutPutFile = "C:\Whatever.xls"   '<----------Your spreadsheet name

' JT Prepare to create a fresh output file by deleting any old files if they exist
    If FileExist(strOutPutFile) Then
        Kill strOutPutFile
    End If

' JT Create the Output Excel spreadsheet
    strSQL = "Select * from MyQueryTable;"  '<-----------Your query table
    Set rsOUT = CurrentDb.OpenRecordset(strSQL)
    Do Until rsOUT.EOF
        strOutQry = rsOUT!("MyQueryFieldName")    '<---------the name of the field in the query table
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strOutQry, strOutPutFile, True
    rsOUT.MoveNext
    Loop
    rsOUT.Close
    Set rsOUT = Nothing

End Function
0
 

Author Comment

by:geraintcollins
ID: 17991835
very good - thanks mate
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 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