I am currently writing ar eport to extract data from a table into seperate excel worksheets and workbooks.
In the simpliest case the table_one has three fields, Account, Region, status.
each region requires it's own Excel file, and each status it's own worksheet. I have managed to perform the simple tasks of exporting the work data from access by manually setting up a code for each of the variable in region and status, but these are constantly changing.
How would i write a query that would extract this data for every varying region and status?
I have been looking around and managed to get to
Expand|Select|Wrap|Line Numbers
- Public Sub search_query()
- Dim results As String
- Dim objXLSheet As Object
- Dim myrecordset As New ADODB.Recordset
- myrecordset.Open "[table_one]", CurrentProject.Connection
- Dim myfield As Object
- For Each myfield In myrecordset.Fields
- Set objXLSheet = objxlapp.worksheets("2")
- Else
- Next
- objXLSheet.Range("a2").CopyFromRecordset myrecordset
- End Sub