asked on

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.

Rey Obrero (Capricorn1)

bsncp

ASKER

Correct me if I am wrong, but doesn't that thread deal with multiple tabs in one spreadsheet? I need to create multiple workbooks.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bsncp

ASKER

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.

PatHartman

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

Rey Obrero (Capricorn1)

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

bsncp

ASKER

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?

bsncp

ASKER

The table I am using is called tblDelete_Test and I have Field 1 and Field 2. Thank you!

PatHartman

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.

bsncp

ASKER

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)

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

PatHartman

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

bsncp

ASKER

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.

PatHartman

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.

bsncp

ASKER

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.

PatHartman

You're welcome.