QueryTable causes Heisenbug (difference between running and debugging code) [SOLVED]

[Amedee Van Gasse]

Hello,

I get this error message when I run the code below:

Run-time error "1004"

The PivotTable field name is not valid. To create a PivotTable,
you
must use data that is organized as a list with labeled columns.
If
you are changing the name of a PivotTable field, you must type a
new
name for the field.

However when I go into debug and restart the code, or when I step
trough the code, there is no error at all!
(This is what I call a Heisenbug: http://en.wikipedia.org/wiki/Heisenbug#Heisenbugs
)

I checked my data, and all columns have headers.

I know the code quality is not optimal. It is "inherited" code, and I
have been messing a bit with to try different things. I'll clean it up
when I get it to work.

Sub Ophalen_Data()

Dim pvtTable As PivotTable
Dim pvtCache As PivotCache

Application.ScreenUpdating = False
Get_Data

On Error Resume Next
ActiveWorkbook.Names.Item("dataRange").Delete
On Error GoTo 0
ActiveWorkbook.Names.Add Name:="dataRange", _
RefersToR1C1:="='Detail HDG'!R1C1:R" & LaatsteRij("Detail
HDG") & "C11"

'Sheets("Pivot HDG").PivotTables("PivotTable1").PivotCache.Refresh
Set pvtTable = Sheets("Pivot HDG").PivotTables(1)
Set pvtCache = pvtTable.PivotCache
'pvtCache.SourceData = "dataRange"
pvtTable.RefreshTable
'pvtCache.Refresh

'Sheets("Per fout").PivotTables("PivotTable2").PivotCache.Refresh
Set pvtTable = Sheets("Per fout").PivotTables("PivotTable2")
Set pvtCache = pvtTable.PivotCache
'pvtCache.SourceData = "dataRange"
pvtTable.RefreshTable
'pvtCache.Refresh

Application.ScreenUpdating = True
Set pvtCache = Nothing
Set pvtTable = Nothing
Sheets("Menu").Activate
End Sub

Function LaatsteRij(sNaamSheet As String) As Integer
LaatsteRij = Sheets(sNaamSheet).Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End Function

Sub Get_Data()
Dim sqlString As String
sqlString = "Select ..." ' querystring obfuscated for obvious
reasons
Sheets("Detail HDG").Activate
ActiveSheet.Cells.ClearContents
With ActiveSheet.QueryTables.Add(Connection:=connstringDB2, _
Destination:=Range("A1"), Sql:=sqlString)
.Refresh
End With
End Sub


I was able to determine that it isn't the PivotTable that is causing
the problem, but the QueryTable from Get_Data. I discovered this after
making the following change to the function LaatsteRij (which I use to
determine the last row of the SourceData range of the PivotTable)


Function LaatsteRij(sNaamSheet As String) As Integer
LaatsteRij = Sheets(sNaamSheet).Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
MsgBox LaatsteRij
End Function


When I run the code, LaatsteRij returns 1. This causes an error.
When I look at the sheet "Detail HDG" (the source for the PivotTable),
I can see that Excel isn't ready with fetching all the data from the
query.
But when I step trough the code in debug, LaatsteRij returns 115, and
all goes well.


My question, dear lazyweb: how can I make sure that my QueryTable is
done fetching data?


EDIT: I found it myself! I had already written this message, but I
hadn't sent it yet.
I thought it would be nice to share it with the world, in case someone
else has a similar issue.

I changed this:

With ActiveSheet.QueryTables.Add(Connection:=connstringDB2, _
Destination:=Range("A1"), Sql:=sqlString)
.Refresh False
End With

Refresh False makes sure the query isn't run in the background, and it
only gives control back to Excel when all data is fetched.