Export MS Access table to multiple Excel workbooks with a module based on value in table field

bsncp
bsncp used Ask the Experts™
on
I have a simple table with two fields that I want to export into multiple Excel Spreadsheets using the value in field one.  I've looked at many different sites and most show how to export the data into multiple tabs of one workbook.  I need multiple workbooks, named useing the values in Field 1.

Field 1         Field 2
Joe               Blue
Joe               Green
Joe               Orange
Eric              Orange
Eric              Orange

For each change in Field 1 value, I want to create a new workbook in a directory, H:\Temp, and name the file using the field 1 value like Joe.xlsx.

Thanks in advance for some coding help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:

Author

Commented:
Correct me if I am wrong, but doesn't that thread deal with multiple tabs in one spreadsheet?  I need to create multiple workbooks.
Top Expert 2016
Commented:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks, but that seems to be overly complicated for what I am trying to do.  I don't understand what is happening in all those sql lines near the middle of the code.  Am I supposed to write a line for every possible value in my first field?  

I am really struggling with understanding how that example matches up with mine....specifically, I can't tell what is similar to my request and what is different.
Distinguished Expert 2017

Commented:
This is of course air code so caveat emptor.  In addition you will need two queries.

qSelectUsers
Select Distinct UserName From YourTable Order By UserName

qExportData
Select * From YourOtherTable Where UserName = Forms!yourform!HiddenUser

Dim db as DAO.Database
Dim qd as DAO.Querydef
Dim rs as DAO.Recordset
Dim PathName as String
Dim FileName as String

Set db = CurrentDB()
Set qd = db.Querydefs!qSelectUsers
Set rs = qd.OpenRecordset

PathName = "C:\somefolder\"
Do Until rs.EOF
    Me.HiddenUser = rs!UserName
    FileName = rs!UserName & ".XLSX"
    docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"qExportData",PathName & FileName,true
    rs.MoveNext
Loop

rs.Close 

Open in new window

Top Expert 2016

Commented:
@bsncp,
what is the actual name of your table and field names?

Author

Commented:
Thank you...this seems to be much closer to what I was expecting.  However, I do only have one table I am working with.  Is there some way I can use sql queries to loop through the table by the distinct values in Field 1, then for each record where the Field 1 value is the same, export them into the file named after the value in Field 1?

Author

Commented:
The table I am using is called tblDelete_Test and I have Field 1 and Field 2.  Thank you!
Distinguished Expert 2017

Commented:
Then the two queries can work against the same table.  The query that controls the loop is the one that determines how the files are named.  The query used in the TransferSpreadsheet determines what gets sent to Excel.

Author

Commented:
Okay, my fault Rey. The exp2XL2 worked perfectly.  I completely misunderstood your post and was trying to reconcile all that code in the post you referenced.  The particular sub is the trick...thank you!
Top Expert 2016

Commented:
here copy and paste the codes to a regular module and run

Sub export2Excel()
 Dim rs As DAO.Recordset, rs1 As DAO.Recordset
 Dim ssql As String, iCol
 Dim xlObj As Object
 Dim Sheet As Object

 Set rs1 = CurrentDb.OpenRecordset("select distinct [Field 1] from tblDelete_Test")

 If rs1.EOF Then Exit Sub
 rs1.MoveFirst


 Do Until rs1.EOF
     Set xlObj = CreateObject("Excel.Application")
     xlObj.Workbooks.Add

     ssql = "select [Field 1],[Field 2] From tblDelete_Test Where [Field 1]='" & rs1![Field 1] & "'"

     Set rs = CurrentDb.OpenRecordset(ssql, dbOpenDynaset)
    
     Set Sheet = xlObj.activeworkbook.Sheets("sheet1")
     'rename the sheet, you can use any of the recordset field
     Sheet.Name = rs1![Field 1]
     'copy the headers
         For iCol = 0 To rs.Fields.Count - 1
             Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
         Next
    
    
     Sheet.Range("A2").CopyFromRecordset rs  'copy the data
    
     xlObj.activeworkbook.SaveAs "H:\Temp\" & rs1![Field 1] & ".xlsx", FileFormat:=51
  
    
     Set Sheet = Nothing
     xlObj.Quit
     Set xlObj = Nothing
 rs1.MoveNext
 Loop
 rs1.Close
 rs.Close
 Set rs1 = Nothing
 Set rs = Nothing
 End Sub

Open in new window

Distinguished Expert 2017

Commented:
I thought you said you wanted a simple solution.  I guess I shouldn't have bothered.

Author

Commented:
Pat, don't be bitter.  Or persnickety, for that matter.  The code Rey provided was the more complete solution and not what I would call complex.  I misread his first post so I thought the code was more complicated than it was.  Your solution called for external queries, neither of which were relevant to the example I submitted.  To be honest, I tried using your solution but couldn't see how to translate your example to my need.   Your solution looks good...if anything, this came down to the answer provided first.   We all are in debt to the valuable knowledge shared here...so I hope you do, in fact, keep bothering.
Distinguished Expert 2017

Commented:
Bitter? No.  Annoyed? Yes.  You said Rey's solution was too complex.  I looked at it and agreed.  You didn't need to do OLE automation to do the task so I wrote something simple that used only Access.  You could have created embedded queries if you don't like to use querydefs.  It is a matter of style but for purposes of example, using querydefs simplified the process.  I also didn't use your column names because no one actually creates columns named Field 1 and Field 2.

Author

Commented:
No, they don't, unless they are trying to simplify their example for the sake of asking for help.  Again, I am grateful for the help.  Just don't understand, or condone, the bashing.  Thanks for the time.
Distinguished Expert 2017

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial