Pause while QueryTables import

[Uncle Dickie]

Hi All,

I have some VB in Excel 2007 which has stopped working since it moved from Excel 2003.

I think the problem is that the QueryTable has not been imported by the time the rest of the code has run but I don't know how to pause!

The variable myProd comes back with 0 and then the For loop does very little!

If I put a break point in the code and then step through a couple of lines it all works fine.

Any pointers would be great:

Private Sub CommandButton1_Click()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Workbooks.Add                       'Create new file for all data
    newname = ActiveWorkbook.Name
    Sheets("Sheet1").Select
 
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=LASSQL01;UID=;APP=Microsoft Office 2003;WSID=PURCHASING5;DATABASE=123_UK;Trusted_Connection=Yes" _
        , Destination:=Range("A1"))
        .CommandText = "exec Lascar.Stock_Profile_Report_Richard"
        .Name = "Query from LascarLive"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery = False
'        .Refresh BackgroundQuery:=False
    End With
 
    myProd = (Application.CountA(Range("A:A")) - 1) / 8      'Number of distinct products to check
 
    For i = 0 To myProd
         Various Formatting Lines
    Next i
End Sub

[Killer42]

I haven't used querytables in Excel, but here are a couple of thoughts.

If you know that it will always return some values, perhaps you could simply loop until myProd comes back non-zero. Something like...

Do
  myProd = (Application.CountA(Range("A:A")) - 1)
Loop Until myProd

(Any non-zero value will count as True and trigger the Until test)

To do a timed delay, perhaps you can write and call a simple function something like...

Public Sub Snooze(ByVal NumSeconds As Integer)
  Dim EndTime As Date
  EndTime = DateAdd("s", NumSeconds, Now)
  Do
    DoEvents
  Loop Until Now >= EndTime
End Sub

Then, after the QueryTables.Add, just put in for example Snooze 5 to pause for 5 seconds.

[Uncle Dickie]

Thanks for the reply.

I have tried your two suggestions as well as a few other variations on the theme and I still have no joy.

I thought it may have been to do with the ScreenUpdating that was disabled but I have tried flicking that on and off during the 'time wasting' loops.

As a final resort I have split the code into 2, the import triggered by one button click and the formatting done once you can see it has loaded (I don't need to worry too much about usability as it is only for my use).

Now the main problem is speed. It used to take about 20s in Excel 2003 but it now takes more than 30minutes in Excel 2007! That's progress!

It seems the conditional formatting is the issue there but I don't think there is much I can do about that as that is what the report is for.

[Killer42]

Hm...

For starters, yes, from experience I'd say that is Microsoft's idea of progress.

To improve performance, you might try turning off automatic calculation before loading, then hit F9 to calculate afterward. However, I doubt that affects conditional formatting.

It might be worth looking for a way to lose the conditional formatting - perhaps by doing the formatting in your code, so it only has to happen once.

Anyway...

I was just browsing your code again. Remember, I haven't used this feature at all, as I said. But I suspect two different problems here.

1. ".BackgroundQuery = True" looks as though it's set the opposite way to what you want.
2. Looking down the end of the code, I also suspect that an unsuccessful attempt to fix the problem is doing the exact opposite of what you intend. If VB is interpreting BackgroundQuery = False as a comparison, then it might return True. Try using ".Refresh False"

It may take a bit of fiddling to work out the interaction between these two parameters.

P.S. To see what I mean about the comparison, go to the Immediate window in the VB editor, and paste in this line...

Print SomeDumbVariable = False