Results 1 to 8 of 8
  1. #1
    dalet is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7

    Export Query to Various Excel Files


    I'm trying to export data from a query to 15 different files and then to specific worksheets in those files depending on the content in column "C". I've created an excel spreadsheet with all the data from the query but still need to move it over to the various files and worksheets.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    A simple working example of what you want to do would help.

    David

  3. #3
    dalet is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7
    I've attached a sample of what the access file is like and also a copy of the excel file that I would like to export too. I'm trying to get it to export to the worksheets based on the Tag# in column C.
    Last edited by dalet; 11-10-2009 at 06:53 AM.

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Few questions:

    What is your vba skills like?
    Do you want the query to be split over worksheets or workbooks?
    What happens if the worksheet does not exist?

    David

  5. #5
    dalet is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7
    very limited vba skills, just learning with access

    Want to break the data down by workbooks and then by worksheets. ie company a has 3 cars we are working on so one workbook for them with a worksheet for each car.
    compang b has 2 cars so another workbook for them with 2 worksheets.

    We would create a new worksheet before exporting the data.

    Quote Originally Posted by dcrake View Post
    Few questions:

    What is your vba skills like?
    Do you want the query to be split over worksheets or workbooks?
    What happens if the worksheet does not exist?

    David

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    What you want is achievable but requires moderate/good knowledge of vba and automation with Excel Objects. Working with recordsets and nested recordsets and calling of public functions from with forms.

    Are you up to it?

    David

  7. #7
    dalet is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7
    Sure, it's the best way to learn.

  8. #8
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Ok.

    Preperation:

    Create a query that groups records by company who have at least one car in for service for the time period specified. This will act as a parent query. Referred to as QryComp

    Create a query grouped by company by car with the information in it that you want to send to Excel. Referred to As QryCars


    Ensure that you have Microsoft DAO referenced on your mdb.
    Ensure that you have Microsoft Excel Objects referenced in your mdb.


    Step 1:

    Create a public function in a standard module named ModExcel

    Step2:

    Create a public function within this module with the following metacode

    Code:
     
    Public Function ExportToExcel()
     
    Dim RsComp As DAO.Recordset
    Dim RsCars As DAO.Recordset
    Dim CompID As Long
    Dim CarsSQL As String
     
    Set RsComp = CurrentDb.OpenRecordset("QryComp")
       If Not RsComp.EOF And Not RsComp.BOF Then
          Do Until RsComp.EOF
             CompID = RsComp("CompanyID")
                'For testing purposes
                Debug.Print "Company Id" & vbTab & CompId
     
                CarsSQL = "Select * From QryCars Where CompanyID = " CompId
                Set RsCars = CurrentDb.OpenRecordset(CarsSQL)
                If Not RsCars.EOF And Not RsCars.BOF Then
                    Do Until RsCars.EOF
                         'For Testing Purposes
                         Debug.Pring "Vehicles " & RsCars("CompId") & vbTab & RsCars("VehicleID")
                         RsCars.MoveNext
                    Loop
                End If
                RsCars.Close
                RsComp.MoveNext
          Loop
          RsComp.Close
       End If
    Set RsComp = Nothing
    Set RsCars = Nothing
     
    End Function
    Use the immediate window to test the results

    You should get:

    Company Id 1
    Vehicle 1 abc123
    Vehicle 1 xcd234
    etc

    Company Id 2
    Vehicle 2 fgrr 567
    Vehicle 2 ptg 683

    etc


    When you have achieved this then get back for the next stage

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. command button to export to excel
    By jains in forum Forms
    Replies: 5
    Last Post: 06-30-2015, 06:27 PM
  2. Export Access Pivot Table to Excel
    By graviz in forum Programming
    Replies: 1
    Last Post: 11-13-2009, 07:30 AM
  3. Replies: 1
    Last Post: 05-28-2009, 05:08 AM
  4. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 AM
  5. Export to Excel
    By vaikz in forum Import/Export Data
    Replies: 3
    Last Post: 03-25-2009, 09:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums