Results 1 to 5 of 5
  1. #1
    thegreatgig is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    2

    Exporting database file into multiple excel files

    I have the below file in my Access database. I need to split this file into several files depending on the column 'vendor code' so as to get one file for each separate vendor code. These files should be directly exported by database.




    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	39.0 KB 
ID:	38222


    I understand that there is a way to do it via VBA in excel, but what I really want to know is if there is a way to get these exports via access itself using modules and VBA so that the export can be a part of my macro.

    Thanks in advance for your help. Also, please help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    create a form ,on it is a listbox of all business units.
    the code will cycle thru the list, and export the query based on the bus.unit
    the query uses the listbox, lstUnit , to pull data, ie:
    select * from table where [busUnit]=forms!myForm!lstUnit
    Code:
    sub btnMake_click()
    dim i as integer
    dim sUnit as string 
    dim vFile
    
    for i = 0 to lstUnit.listcount - 1
       sUnit = lstUnit.ItemData(i)   'get next item in list
       lstUnit = sUnit              'set list to that item for the query to read
       vFile = "c:\temp\" & sUnit & ".xlsx"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitPayments", vFile, True,"Payments"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitChrgs", vFile, True,"Charges"
    next
    end sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might take a look at Ken Snell's site http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    There are many examples of exporting to Excel.....

  4. #4
    thegreatgig is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    2
    This link is so helpful. Thanks a lot!

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're welcome.....

    And welcome to the forum...

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

Similar Threads

  1. Replies: 34
    Last Post: 07-02-2015, 01:45 AM
  2. Replies: 20
    Last Post: 02-02-2015, 03:02 AM
  3. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  4. Replies: 1
    Last Post: 09-13-2013, 12:00 PM
  5. Replies: 7
    Last Post: 08-05-2011, 10:59 AM

Tags for this Thread

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