Very Hard Problem! Exporting queries with variables. 1

[Rubius]

This is what I need to do:<br>I have a table, the table has a bunch of entries that are sorted by a vendor. Now each vendor will have 1 to a bunch of entries, but not every vendor is on the list. What I did so far is create a query that lists the vendors on the table and no dupes. Another query, asks for a vendor and then shows the entries corresponding to the vendor. Easy so far? Yes. <br>Here's where it gets hard. I need code that will automatically go through the list of vendors, run the query that lists the entries by vendor, and export each of those queries into excel 97 format, with each filename having the name of the vendor. all in all it should create one file for each vendor, doesn't matter if there are 1 to 100 files created, just need a seperate file for each vendor.<br>Any questions, please let me know!!!!!<br><br>Thanks in advance!

 

[JimConrad]

Do you know how to write VBA code?<br><br>Create a recordset in code to get the list of vendor names.<br><br>Loop through the recordset and run a &quot;make table&quot; query to select the data you need for the given vendor.<br><br>Then export the data using DoCmd.TransferSpreadsheet. <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>

 

[DougP]

Piece of CAKE<br><br>------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset, SQL As String, a&nbsp;&nbsp;As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ExcelVendorName As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = &quot;&nbsp;&nbsp;&nbsp;Put your Queries SQL ocde in here between these quotes&nbsp;&nbsp;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(SQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;For a = 1 To rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ExcelVendorName = &quot;C:\ExcelSheets\&quot; & rst!VendorName & &quot;.xls&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;Tablename&quot;, ExcelVendorName, True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>--------------------------- <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Rubius]

Doug!<br>So very close!!<br>DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;Tablename&quot;, ExcelVendorName, True<br><br>I need to make it look at a different Tablename every loop. Because just like how the filename changes every time as by the vendor, the table needs to change so it has only the one vendors information. Basically the table needs to be based on a query, which is based on the vendor field of the orginal SQL query.<br>Files need to look like this:<br>xxx-123.xls<br>(inside file)<br>xxx-123's info<br><br>yyy-456.xls<br>(inside file)<br>yyy-456's info<br><br>thanks

 

[DougP]

Well you have to get the table names from somewhere.<br>this code will get the table names and fields which you don't need. But you have to disect it and plug it into your code.<br><br>Dim dbsNorthwind As Database<br> Dim fldLoop As Field<br> Dim relLoop As Relation<br> Dim tdfloop As TableDef<br><br> Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot<br><br> With dbsNorthwind<br><br> ' Display the attributes of a TableDef object's <br> ' fields.<br> Debug.Print &quot;Attributes of fields in &quot; & _<br> .TableDefs(0).Name & &quot; table:&quot;<br> For Each fldLoop In .TableDefs(0).Fields<br><br>Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;&quot; & fldLoop.Name & &quot; = &quot; & _<br> fldLoop.Attributes<br> Next fldLoop<br><br> ' Display the attributes of the Northwind database's <br> ' relations.<br> Debug.Print &quot;Attributes of relations in &quot; & _<br> .Name & &quot;:&quot;<br> For Each relLoop In .Relations<br> Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;&quot; & relLoop.Name & &quot; = &quot; & _<br> relLoop.Attributes<br> Next relLoop<br><br> ' Display the attributes of the Northwind database's <br><br>' tables.<br> Debug.Print &quot;Attributes of tables in &quot; & .Name & &quot;:&quot;<br> For Each tdfloop In .TableDefs<br> Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;&quot; & tdfloop.Name & &quot; = &quot; & _<br> tdfloop.Attributes<br> Next tdfloop<br><br> .Close<br> End With<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.