Export to Excel making Multiple Tabs

[Guest]

Two tables that are related by the Boat Model make up a query. The tblModel
has anywhere from 9 - 12 boats and the tblParts has from 12 - 12,000 parts
per boat.

What I need to do is write an Array and/or Loop to cycle through the
tblParts and filter by each Model - then export to excel the parts for that
model into a workbook. So my workbook would have between 9 - 12 worksheets
based on how many boats are in the Models Table.

Is this possible or am I think to far ahead?

Thanks.

 

[Nikos Yannacopoulos]

Steve,

I'll assume field ModelID being the PK in tblModel, and foreign key in
tblParts (and you'll have to substitute the actual name).

Start by saving this query, named qryPartsExport:

SELECT * FROM tblParts WHERE ModelID = Selected_Model()

(start making a new query in design view, add no table, revert to SL
view and paste the above expression; make sure the PK field name is
correct!)

Then paste the following code in a general module:

Public vModel as String

Sub Export_Parts()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vWorkbook As String
vWorkbook = "C:\My Documents\Boat Parts.xls" 'or whatever
Set db = CurrentDb
Set rst = db.OpenRecordset("tblModels")
Do Until rst.EOF
vModel = rst.ModelID
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, & _
qryPartsExport, vWorkbook, , vModel
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Function Selected_Model()
Selected_Model = vModel
End Function

Again, make sure you have the correct PK field name. Running Sub
Export_Parts() will export one sheet per model, naming it after the
ModelID; just make sure you don't have any funny chracters in the
ModelID (spaces, dashes and underscores work OK).

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos

 

[Guest]

Nikos,

This did precisely what I was looking for, I only made a few minor
modifcations to fit into my current mod's and if turned it right out the
first time...

I just basically added your loop to my current code and I now understand
what that Range option is for an Export.

Thanks Muy Mucho Gracias...

-Steve

 

[Nikos Yannacopoulos]

Steve,

Glad it worked for you. By the way, this use of the Range argument is
not documented by Microsoft; if you check out TransferSpreadsheet in
help, it says that Range applies to Import only!!! I suspect this is
because they never got it to perfection - which is why I warned on the
use of funny charatcers in Range).

Regards,
Nikos