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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
bsncpAuthor 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.
Rey Obrero (Capricorn1)Commented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

bsncpAuthor 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.
This is of course air code so caveat emptor.  In addition you will need two queries.

Select Distinct UserName From YourTable Order By UserName

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


Open in new window

Rey Obrero (Capricorn1)Commented:
what is the actual name of your table and field names?
bsncpAuthor 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?
bsncpAuthor Commented:
The table I am using is called tblDelete_Test and I have Field 1 and Field 2.  Thank you!
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.
bsncpAuthor 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!
Rey Obrero (Capricorn1)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

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

     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
     Sheet.Range("A2").CopyFromRecordset rs  'copy the data
     xlObj.activeworkbook.SaveAs "H:\Temp\" & rs1![Field 1] & ".xlsx", FileFormat:=51
     Set Sheet = Nothing
     Set xlObj = Nothing
 Set rs1 = Nothing
 Set rs = Nothing
 End Sub

Open in new window

I thought you said you wanted a simple solution.  I guess I shouldn't have bothered.
bsncpAuthor 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.
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.
bsncpAuthor 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.
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.