Automate Export to Numerous XLS Files

[TommyP]

Hi All.

I cannot figure this one out and was wondering if anyone knows if this is possible.

Scenario: Multiple vendors with many materials in a table.

Vendor Material
1 A
1 B
1 C
2 X
2 Y
3 Z

Is it possible to export vendor ones materials to vendor1.xls and vendor two's materials to vendor2.xls and vendor three's materials to vendor3.xls automatically? Would I do this with a macro, vb code, query?

Thanks in advance.

 

[M8KWR]

it would be best to use code.

You can automatically pick the tables or query, then either manually or automatically generate the file name, and the path. But this depends on what you want to do.

If you just want to export the queries/tables then you would use something like:-

    Dim PathName1

    Pathname1 = "vendor1.xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Query or table name", Pathname1, True

    Pathname1 = "vendor2.xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Query or table name", Pathname1, True

'and so on, you could also use a loop if you wanted.

Hope this points you in the right direction if not, or you require more help let me know.

 

[KenReay]

Hi

Using DAO (reference to DAO library needed)

You will need to have a (Select) query defined called qQuery

I have guessedat your table names you need to use your own table names.

Below may not be the only way or the most elegant, but it will get you started:


Dim strSQL as String
Dim Db as DAO.Database
Dim RsV as DAO.Recordset
Dim RsM as DAO.Recordset
Dim qdf as QueryDef
'
Set Db = CurrentDB()
Set RsV = db.openRecordset("SELECT * FROM tblVendors;")
If RsV.RecordCount > 0 Then
Do Until RsV.EOF
strSQL = "SELECT * FROM tblMaterials WHERE VendorId = " & RsV!VendorId & ";")
set qdf = Db.QueryDefs("qQuery")
qdf.SQL = strSQL
docmd.OutputTo acOutputQuery,qQuery,,,false
RsV.MoveNext
Loop
End If
RsV.Close
Set RsV = Nothing
Set qdf = Nothing
Set Db = Nothing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK