Looping through Saved queries in VBA

[JeffHaynes]

HI there. I have about 20 saved queries in an Access MDB and I want to run each one and put the results into seperate worksheets in an Excel workbook using a VBA module (probable from a command button on a form). What is the VBA code necessary to do this? How do I loop through each one without hard-coding the name of each query? And how do I put the results into seperate worksheets. Many thanks

 

[RoyVidar]

This looks very much like "please make my appliation for me", which I don't believe is the intention of this site. It's more about helping out when there are problems, at least as I've perceived it.

For this, though there has been umpteen threads on the topics - I'd suggest a search. Looping the querydefs collection is one thing, then there are all the other methods - Transfer<thingies> of the DoCmd object, automating Excel ...

Post back when you have specific problems with the code you find ...

Roy-Vidar

 

[JeffHaynes]

Searching proved fruitless hence my post.

 

[JeffHaynes]

Finally did it using the xlWS.Range("A2").CopyFromRecordset rsAll method. However it did not copy overthe column headers. Any ideas?

 

[PWise]

this will give you all the queries in the database

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([name],1))<>"~"));

but you still have to watch for append ,update queries ect

what i whould do is create a table with the names of the queries that i want to run and in code open a recordset with the names of the queries and loop thru the recordset and run whatever code you want on each code

 

[RoyVidar]

My Excel programming is a bit rusty, but something along these lines, perhaps

[tt]dim lngCount as long
for lngCount = 0 to rs.fields.count-1
xlWS.cells(1, lngCount+1).value = rs.fields(lngcount).name
next lngCount[/tt]

Nice work!

Roy-Vidar

 

[JeffHaynes]

In the end I used (as they were all select queries):
For Each qryDef In db.QueryDefs
DBTable = qryDef.Name

cmd.CommandText = DBTable
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
'Count the number of fields or column
MyFieldCount = rs.Fields.Count
Set xlWS = ApExcel.Worksheets.Add
xlWS.Name = DBTable

'Fill the first line with the name of the fields
For MyIndex = 0 To MyFieldCount - 1

'Draw border on the title line
MyCol = Chr((64 + MyIndex)) & InitRow
'ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, 0, 0)
MyRecordCount = 1 + InitRow

'Fill the excel book with the values from the database
Do While rs.EOF = False
For MyIndex = 1 To MyFieldCount
ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs(MyIndex - 1).Value 'Write Value to a Cell
ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the Cell
Next
MyRecordCount = MyRecordCount + 1
rs.MoveNext
If MyRecordCount > 50 Then
Exit Do
End If
Loop
ApExcel.DisplayAlerts = False
For a = ApExcel.Sheets.Count To 1 Step -1
If Left(ApExcel.Sheets(a).Name, 5) = "Sheet" Then
If ApExcel.Sheets.Count = 1 Then
Else
ApExcel.Sheets(a).Delete
End If
End If
Next a

Next
ApExcel.Visible = True
'Suggest to the user to save it's work
Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save your file")

'Close the connection with the DB
rs.Close


ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name 'Write Title to a Cell
ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
Next

Etc. Etc