Fordraiders
asked on
Export Multiple Queries to excel and append to one sheet
access 2003
excel 2003
vba function needed
workbook name : C:\Program Files\MyWork\MyResults.xls
I have about 15 queries to append to a worksheet("ResultData")
All with various Names:
Export Status Codes
Export Red Codes
Export Resgualr Codes
etc..
But these names I could put in a text file
to loop through?
I need a VBA routine to append each query and its field headers to append to each other.
Thanks
fordraiders
excel 2003
vba function needed
workbook name : C:\Program Files\MyWork\MyResults.xls
I have about 15 queries to append to a worksheet("ResultData")
All with various Names:
Export Status Codes
Export Red Codes
Export Resgualr Codes
etc..
But these names I could put in a text file
to loop through?
I need a VBA routine to append each query and its field headers to append to each other.
Thanks
fordraiders
* save the name of the queries in a table "tblQueries" with Field Name "QueryName"
use this code
use this code
Dim db As DAO.Database, rs As DAO.Recordset, rsQ As DAO.Recordset
Dim strQry As String
Dim xlObj As Object
Dim Sheet As Object, rowCnt As Integer
Dim iRow, iCol
Set db = DBEngine.Workspaces(0).Databases(0)
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open ("C:\Program Files\MyWork\MyResults.xls")
Set Sheet = xlObj.ActiveWorkbook.Sheets("ResultData")
rowCnt = 0
Set rsQ = db.OpenRecordset("tblQueries")
rsQ.MoveFirst
Do Until rsQ.EOF
iRow = rowCnt + 1
strQry = rsQ!queryName
Set rs = db.OpenRecordset(strQry)
'copy the headers
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(iRow, iCol + 1).Value = rs.Fields(iCol).Name
Next
'this copy just the data
Sheet.range("A" & iRow + 1).CopyFromRecordset rs
'check how many rows where populated
rowCnt = Sheet.usedrange.rows.Count
rsQ.MoveNext
Loop
'save the file
xlObj.ActiveWorkbook.Save
rsQ.Close
rs.Close
Set Sheet = Nothing
xlObj.Quit
Set xlObj = Nothing
ASKER
no, its the same fields
you can use a union query then export the result, but i'll prefer using the vba codes posted at http:#a36500453
did you try it?
did you try it?
ASKER
gonna try now...Thanks the second piece of code http:#a36500453
ASKER
cap, Error here
Do Until rsQ.EOF
iRow = rowCnt + 1
strQry = rsQ!queryName
Set rs = db.OpenRecordset(strQry) <--- name not available error 3265 Item not found in this collection.
I have 4 querynames in the table
but strQry = ""
Do Until rsQ.EOF
iRow = rowCnt + 1
strQry = rsQ!queryName
Set rs = db.OpenRecordset(strQry) <--- name not available error 3265 Item not found in this collection.
I have 4 querynames in the table
but strQry = ""
what is the name of the field in your table "tblQueries"
ASKER
"UnsolicatedQueries"
can you upload a copy of the db (.mdb version )
ASKER
it has links to network tables..?
ASKER
hold on a sec...
ASKER
its 64mb zipped...but I could delete the data ?
open the tblQueries, copy the contents by selecting all of columns and fields and post it here..
did you do a compact and repair before zipping?
yes you can delete some the data, but not the records from tblQueries
yes you can delete some the data, but not the records from tblQueries
ASKER
ok.. I got your first bit of code to work fine...
here is the database...
testing-Prmdb025-chgo-Reporting-.zip
here is the database...
testing-Prmdb025-chgo-Reporting-.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks as always ! very much !
Great
Great
you can start with these codes
Open in new window
* Note : you can place the names of the queries in an Array or in a table