PDA

View Full Version : Help with Error 91



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

Bob Phillips
12-15-2010, 01:33 PM
Just use



ActiveWorkbook.RefreshAll

dbmagnus
12-15-2010, 01:41 PM
That works. Of course it doesn't do the fancy display of which query of how many are refreshing, though in reality I don't think that is even necessary.

Thanks for the reply.

Blade Hunter
12-15-2010, 04:48 PM
Is it because you have not assigned anything to your variable: shtData?

maybe use: shtData = activesheet (If this is the case it negates the need for shtData altogether anyway)

dbmagnus
12-16-2010, 01:12 PM
The response by xld was still giving me some problems, so I played around with it some more and I think I found the code the for now seems to be working the best:

Public Sub refreshtest2()
Dim qt As QueryTable
For Each qt In Worksheets("Data").QueryTables
qt.refresh
Next

End Sub

Now if someone could just tell me how to clear the contents of a cell once a new date is selected in one of the Calendar Controls that would be helpful (though that may require a separate post).