Disappearing Query Tables

[SkipVought]

First time in over 10 years of using QueryTables in Excel.

An application that I have been working on for several weeks as lost several QueryTables all at once, it seems.

As a matter of course, I code each QT, since I often substitute in my SQL and sometimes my Connection: one QT per sheet. Example...

Sub GetRespCodeUnion()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = ActiveWorkbook.Path
    
    sDB = Split(ActiveWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT DISTINCT Resp_CODE"
    sSQL = sSQL & ", 'RqmtDate'"
    sSQL = sSQL & ", 'RQQTY_275' "
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Data$` "
    sSQL = sSQL & "UNION "
    sSQL = sSQL & "SELECT DISTINCT Resp_CODE"
    sSQL = sSQL & ", 'IssuDate'"
    sSQL = sSQL & ", 'ISSUEQTY_275' "
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Data$` "
    
    With wsRC_Week.QueryTables("qryRC_Week")
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
        Application.DisplayAlerts = False
        .ResultRange.CurrentRegion.CreateNames True, False, False, False
        Application.DisplayAlerts = True
    End With
End Sub

Any thoughts?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[xlbo]

Might be an obvious question but do you remember what you were doing just before you noticed that the querytables had gone ??

any chance there has been an accidental copy/pastespecial>values ??

I have had that happen when I forgot to explicitly reference a sheet and just did a copy/paste values. It applied it to the active sheet rather than the sheet I thought it would do and got rid of a whole bunch of formulae etc.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

3 tables on 3 different sheets. Normally I don't even activate these sheets. But I had Calculation Manual, an I would activate each sheet in order to Tools/Options/Calc Tab [Calc_Sheet].

I had been doing this earlier. But then executing the code, got an object error on the refresh code statement. Truns out in some cases the Refers To in Insert/Names/Define for the QT Range name simply had
[tt]
=
[/tt]




Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[xlbo]

how odd - sorry Skip - never seen that before

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

...and, oddly enough, when I deleted the Range Name in the Define window, Excel still detected that that name existed. I had to shut down the application and opent the workbook before it freed that Range Name.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}