[Excel 2003] Runtime Error 7 (Out of Memory) With QueryTable.Refresh BackgroundQuery:=False

[elleg007]

First, a little background info...

Where I work, most users have to connect to a specific server (via mstsc--so, remote desktop) in order to have an OBDC connection (i.e. run queries). I happen to have the Oracle client installed on my desktop, so I have no need to connect to this server.

I am running Windows XP, and Excel 2003. The server everyone else has to use is running Windows Server 2003, and also has Excel 2003.

I have a single macro in which I refresh two embedded queries (query tables) on two different worksheets. On my computer, this macro runs just fine, but on the server, I get a 'Runtime Error 7' (Out of memory) on the line trying to refresh the first query. If I comment out that line, it also gets the same error on the next line which is refreshing the second query.

Here's my code:

Sub Refresh_Queries()
    Dim wksPM As Worksheet
    Dim wksMS As Worksheet
    
    On Err GoTo e1
    Set wksPM = ThisWorkbook.Sheets("PM List")
    Set wksMS = ThisWorkbook.Sheets("MS Details")
    
    On Err GoTo e2
    
    Application.Calculation = xlCalculationManual
'---these are the two problem lines
    wksPM.Range("Query_for_PM").QueryTable.Refresh BackgroundQuery:=False
    wksMS.Range("Query_for_MS").QueryTable.Refresh BackgroundQuery:=False
'---------------------------------
    Application.Calculation = xlCalculationAutomatic
    
    Set wksPM = Nothing
    Set wksMS = Nothing
    Exit Sub
e1:
    Set wksPM = Nothing
    Set wksMS = Nothing
    Call error_1
    Exit Sub
e2:
    Set wksPM = Nothing
    Set wksMS = Nothing
    Call error_2
End Sub

If I comment out these bits: "BackgroundQuery:=False", the macro runs just fine. However, I kind of need to have that set to false, as I call this macro in a series of macros. The macro that gets called after this one protects all the sheets in the workbook. Without that parameter set to false, it protects the sheets before the queries finish running, and then when the queries finish, they can't update the sheets with the new data because the sheets are protected.

Any ideas?

 

[elleg007]

Right now, it's looking as if I'm going to have to comment out BackgroundQuery:=False and make the user press a second button once they verify the queries are finished. Not the ideal solution, but it'll work. Any chance someone knows how to deal with this 'Out of Memory' error?