<div>
see this similar thread<br />
<br />
<a href="https://www.experts-exchange.com/questions/22687254/Export-Access-Data-to-Excel-Parsed-by-a-Specific-Field.html?&anchorAnswerId=19467728#a19467728" rel="ugc">https://www.experts-exchange.com/questions/22687254/Export-Access-Data-to-Excel-Parsed-by-a-Specific-Field.html?&anchorAnswerId=19467728#a19467728</a>
</div>


<div>
Correct me if I am wrong, but doesn't that thread deal with multiple tabs in one spreadsheet? &nbsp;I need to create multiple workbooks.
</div>


<div>
Thanks, but that seems to be overly complicated for what I am trying to do. &nbsp;I don't understand what is happening in all those sql lines near the middle of the code. &nbsp;Am I supposed to write a line for every possible value in my first field? &nbsp;<br />
<br />
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.
</div>


<div>
This is of course air code so caveat emptor. &nbsp;In addition you will need two queries.<br />
<br />
qSelectUsers<br />
Select Distinct UserName From YourTable Order By UserName<br />
<br />
qExportData<br />
Select * From YourOtherTable Where UserName = Forms!yourform!HiddenUser<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41530698-1"><span>Dim db as DAO.Database</span>
<span>Dim qd as DAO.Querydef</span>
<span>Dim rs as DAO.Recordset</span>
<span>Dim PathName as String</span>
<span>Dim FileName as String</span>
<span></span>
<span>Set db = CurrentDB()</span>
<span>Set qd = db.Querydefs!qSelectUsers</span>
<span>Set rs = qd.OpenRecordset</span>
<span></span>
<span>PathName = &quot;C:\somefolder\&quot;</span>
<span>Do Until rs.EOF</span>
<span>    Me.HiddenUser = rs!UserName</span>
<span>    FileName = rs!UserName &amp; &quot;.XLSX&quot;</span>
<span>    docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,&quot;qExportData&quot;,PathName &amp; FileName,true</span>
<span>    rs.MoveNext</span>
<span>Loop</span>
<span></span>
<span>rs.Close </span>
</code></pre>
</div>


<div>
@bsncp,<br />
what is the actual name of your table and field names?
</div>


<div>
Thank you...this seems to be much closer to what I was expecting. &nbsp;However, I do only have one table I am working with. &nbsp;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?
</div>


<div>
The table I am using is called tblDelete_Test and I have Field 1 and Field 2. &nbsp;Thank you!
</div>


<div>
Then the two queries can work against the same table. &nbsp;The query that controls the loop is the one that determines how the files are named. &nbsp;The query used in the TransferSpreadsheet determines what gets sent to Excel.
</div>


<div>
Okay, my fault Rey. The exp2XL2 worked perfectly. &nbsp;I completely misunderstood your post and was trying to reconcile all that code in the post you referenced. &nbsp;The particular sub is the trick...thank you!
</div>


<div>
here copy and paste the codes to a regular module and run<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41530763-1"><span>Sub export2Excel()</span>
<span> Dim rs As DAO.Recordset, rs1 As DAO.Recordset</span>
<span> Dim ssql As String, iCol</span>
<span> Dim xlObj As Object</span>
<span> Dim Sheet As Object</span>
<span></span>
<span> Set rs1 = CurrentDb.OpenRecordset(&quot;select distinct [Field 1] from tblDelete_Test&quot;)</span>
<span></span>
<span> If rs1.EOF Then Exit Sub</span>
<span> rs1.MoveFirst</span>
<span></span>
<span></span>
<span> Do Until rs1.EOF</span>
<span>     Set xlObj = CreateObject(&quot;Excel.Application&quot;)</span>
<span>     xlObj.Workbooks.Add</span>
<span></span>
<span>     ssql = &quot;select [Field 1],[Field 2] From tblDelete_Test Where [Field 1]='&quot; &amp; rs1![Field 1] &amp; &quot;'&quot;</span>
<span></span>
<span>     Set rs = CurrentDb.OpenRecordset(ssql, dbOpenDynaset)</span>
<span>    </span>
<span>     Set Sheet = xlObj.activeworkbook.Sheets(&quot;sheet1&quot;)</span>
<span>     'rename the sheet, you can use any of the recordset field</span>
<span>     Sheet.Name = rs1![Field 1]</span>
<span>     'copy the headers</span>
<span>         For iCol = 0 To rs.Fields.Count - 1</span>
<span>             Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name</span>
<span>         Next</span>
<span>    </span>
<span>    </span>
<span>     Sheet.Range(&quot;A2&quot;).CopyFromRecordset rs  'copy the data</span>
<span>    </span>
<span>     xlObj.activeworkbook.SaveAs &quot;H:\Temp\&quot; &amp; rs1![Field 1] &amp; &quot;.xlsx&quot;, FileFormat:=51</span>
<span>  </span>
<span>    </span>
<span>     Set Sheet = Nothing</span>
<span>     xlObj.Quit</span>
<span>     Set xlObj = Nothing</span>
<span> rs1.MoveNext</span>
<span> Loop</span>
<span> rs1.Close</span>
<span> rs.Close</span>
<span> Set rs1 = Nothing</span>
<span> Set rs = Nothing</span>
<span> End Sub</span>
</code></pre>
</div>


<div>
I thought you said you wanted a simple solution. &nbsp;I guess I shouldn't have bothered.
</div>


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


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


<div>
No, they don't, unless they are trying to simplify their example for the sake of asking for help. &nbsp;Again, I am grateful for the help. &nbsp;Just don't understand, or condone, the bashing. &nbsp;Thanks for the time.
</div>


<div>
<div class="content wysiwyg-content">
I have a simple table with two fields that I want to export into multiple Excel Spreadsheets using the value in field one. &nbsp;I've looked at many different sites and most show how to export the data into multiple tabs of one workbook. &nbsp;I need multiple workbooks, named useing the values in Field 1.<br />
<br />
Field 1 &nbsp; &nbsp; &nbsp; &nbsp; Field 2<br />
Joe &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Blue<br />
Joe &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Green<br />
Joe &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Orange<br />
Eric &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Orange<br />
Eric &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Orange<br />
<br />
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.<br />
<br />
Thanks in advance for some coding help.
</div>
</div>


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.

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.