Querytables? What Querytables?? - Excel (1 Viewer)

[JamesMcS]

G'day all, long time... Hope everyone's well!

I've got this spreadsheet with four tabs on it. Three of them have querytables looking at an Access DB and all is well. But...

I want to change some of the criteria in a couple of the queries to reflect what's in a particular cell on the spreadsheet - this bit I can do I think, by changing the commandtext for each of them. However I can't reference the querytables themselves - it seems as though Excel thinks there are no querytables in any of the sheets! The following code returns 0 for each sheet:

Dim Wkbk As Workbook
Dim Wksht As Worksheet
Dim QTbl As QueryTable

Set Wkbk = ThisWorkbook

For Each Wksht In Wkbk.Worksheets

    Debug.Print Wksht.Name
    Debug.Print Wksht.QueryTables.Count
        
Next Wksht


End Sub

Where am I going wrong guys??

Edit: (I should mention that the queries I want to change were created with Microsoft Query) (Oh, and I'm using Access and Excel 2007)

 

[Trevor G]

James Hi hope your well.

I use the following code to query an Access Database based on a drop down I use in a cell. It clears the contents first and then imports the query. I wonder if this will help you.

Sub RunParameterQuery()

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("M:\Access Files\Test ME Today.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryGoToExcel") 'Query name in the database

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Department]") = Range("D3").Value 'From parameter field in access

End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
Cells.EntireColumn.AutoFit

MsgBox "Query has been successful", vbInformation, "Sample"

End Sub

 

[JamesMcS]

Hi Trevor, thanks for the post, life is treating me generally well

I'll have a play around with that and see where it gets me. For now I've gone the other way - created queries in Access with variables for criteria and am cycling through a recordset as I open and close all the spreadsheets (there are hundreds of them).

Cheers again, enjoy the rest of the day!