Warning:: I am very new to programming, having never had to use VBA before, traditionally i am a SQL/Crystal Reports writer... with that in mind:
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
-
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
-
However this produces the first row from each column, maybe i need more books!
