dbmagnus
12-15-2010, 01:18 PM
This is probably an easy question, but my novice experience and lack of applicable search results have left me in a bind. I'm trying to write a simple macro that when run will refresh X number of data blocks (queries) in the file.
First the error:
Run time error 91: Object variable or With block variable not set.
Now the code:
Public Sub refresh_report()
Dim ct As Integer
Dim i As Integer
Dim qt As QueryTable
Dim shtData As Worksheet
Dim shtReport As Worksheet
ct = shtData.QueryTables.Count
i = 0
For Each qt In shtData.QueryTables
i = i + 1
shtReport.Range("B21").Value = "Refreshing data block " & i & " of " & ct & "..."
qt.BackgroundQuery = False
qt.Refresh
Next qt
shtReport.Range("B21").ClearContents
End Sub
It is erroring here:
ct = shtData.QueryTables.Count
I would think there's any easy solution but I haven't found it yet. Thanks for any help you could give this novice.
-Dan
First the error:
Run time error 91: Object variable or With block variable not set.
Now the code:
Public Sub refresh_report()
Dim ct As Integer
Dim i As Integer
Dim qt As QueryTable
Dim shtData As Worksheet
Dim shtReport As Worksheet
ct = shtData.QueryTables.Count
i = 0
For Each qt In shtData.QueryTables
i = i + 1
shtReport.Range("B21").Value = "Refreshing data block " & i & " of " & ct & "..."
qt.BackgroundQuery = False
qt.Refresh
Next qt
shtReport.Range("B21").ClearContents
End Sub
It is erroring here:
ct = shtData.QueryTables.Count
I would think there's any easy solution but I haven't found it yet. Thanks for any help you could give this novice.
-Dan