urgent help needed. Thanks a lot! for loop in module to run the same query many times

[geneS]

Dear All,

I am new to module and VBA.




My database has 200 ID and associated hourly data for each ID. I want to extract hourly data for each ID to Excel file. Currently, I run the query and copy-paste the result to an Excel worksheet. Then change ID in the query and run again a with a particular UnitID and then export it to Excel (or copy-paste). There are more than 200 ID. It would be too time-consuming to do it manually. I don't know enough about Macros but my intuition is there might be a customer-written Macro looping though all 200 units running the same query with different UnitID and export the result automatically to the same Excel files (to different worksheet). Thanks!

Gene

[June7]

Not macro, VBA.

Each UnitID must be on separate worksheets in same workbook?

Looping code to export each set of UnitID records to separate workbooks is easy with the TransferSpreadsheet or OutputTo methods. Exporting to a new sheet of same workbook is trickier. Requires opening workbook as an object in VBA and manipulating that object. Lots of web references on this. Here's one http://www.utteraccess.com/forum/exp...o-t539561.html

[geneS]

Thanks a lot!!

I am new to VBA. Can I store a vector of 200 Unit ID in a variable is VBA? e.g., Dim Unit_ID_var = [203, 209, 289, ..., 98553]
then loop through Unit_ID_var by Unit_ID_var(i) where i goes from 1 to 200.

The Table or Query contains records for 200 Unit ID. My goal is to export each set of UnitID records to separate worksheet. The link you provided is very inspiring. Thanks a lot! I need to learn more about the syntax.

[June7]

A variable is one way to control a loop.

For i = UnitID_var To UnitID_var + 200
'do this code
Next i

Or open a recordset and loop the records

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT UnitID FROM tablename;")
rs.MoveFirst
While Not rs.EOF
'code to open another recordset filtered to the UnitID of current record in rs and write data to Excel
rs.MoveNext
Wend