Add querytable 1

[johnny45]

Hello,
I'm using th efollowing to add a query to an excel worksheet:

        With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))
            .CommandText = sSql
            .Refresh BackgroundQuery:=False
        End With

This works, however if I change to another sheet I get a run time error # 5.....

How can I modify the above so I can insert different query tables to different sheets in workbook?

 

[SkipVought]

Hi,

Missing Sheet Object reference...

        With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=[b]ActiveSheet.[/b]Range("A1"))
            .CommandText = sSql
            .Refresh BackgroundQuery:=False
        End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I always question code that ADDS a QueryTable object to a sheet. I use QT a bunch, and I rarely ADD in VBA. I usually set up my queries and then use VBA to modify either the SQL or the CONNECTION & SQL using the existing QT object.

I hope that you do not ADD a QT to your sheet each time you run this code, which will happen.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnny45]

Skip,
Thank you for the insight.
I do run this before:

For Each qtb In Sheet1.QueryTables
qtb.Delete
Next

can you outline what you mean by
" I usually set up my queries and then use VBA to modify either the SQL or the CONNECTION & SQL using the existing QT object.

 

[SkipVought]

Using Data > Import External Data, I set up my query, using the QBE grid and verify that I am getting the data returned that I need. In MS Query, I Edit > Return data to Microsoft Excel.

Now I have a QueryTable object on my sheet that can be Data > Refershed at any time.

In the VB Editor, I can execute this code to display the Connection & CommandText...

Sub Query()
  With MySheetObject.QueryTables(1)
    Debug.Print .Connection
    Debug.Print .CommandText
  End With
End Sub

and copy the text from the Immediate Window.

Then I modify to something that might end up like this where the PATH can "automatically" change when I move my files and a user supplied value satisfies one of the criteria...

Sub Query(sPN as string)
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = "OrderRelease"
    
    sConn = "ODBC;DSN=MS Access Database;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "Transform  SUM(CHT_QTY)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  CHT_PN          As TW_PN"
    sSQL = sSQL & ", PM.PM_NOM       As TW_NOM"
    sSQL = sSQL & ", PM.PM_GP        As TW_GP"
    sSQL = sSQL & ", PM.PM_RC        As TW_RC"
    sSQL = sSQL & ", PM.PM_MIN       As TW_MIN"
    sSQL = sSQL & ", left(PM.PM_MAX,3) AS TW_MAX"
    sSQL = sSQL & ", PM.PM_OP        As TW_OP"
    sSQL = sSQL & ", PM.PM_POS       As TW_POS"
    sSQL = sSQL & ", PM.PM_TQ        As TW_TQ"
    sSQL = sSQL & ", PM.PM_RD        As TW_RD"
    sSQL = sSQL & ", PM.PM_OD        As TW_OD"
    sSQL = sSQL & ", PM.PM_CM        As TW_CM"
    sSQL = sSQL & ", PM.PM_SP        As TW_SP"
    sSQL = sSQL & ", INT(PM.PM_MATL) as TW_MATL"
    sSQL = sSQL & ", CHT_Cat         As TW_CAT"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  `" & sPath & "\" & sDB & "`.GetJoin GetJoin"
    sSQL = sSQL & ", `" & sPath & "\" & sDB & "`.PM PM"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Where CHT_PN = PM_PN"
    sSQL = sSQL & "  AND CHT_PN='" & sPN & "'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Group By"
    sSQL = sSQL & "  CHT_PN"
    sSQL = sSQL & ", PM.PM_NOM"
    sSQL = sSQL & ", PM.PM_GP"
    sSQL = sSQL & ", PM.PM_RC"
    sSQL = sSQL & ", PM.PM_MIN"
    sSQL = sSQL & ", left(PM.PM_MAX,3)"
    sSQL = sSQL & ", PM.PM_OP"
    sSQL = sSQL & ", PM.PM_POS"
    sSQL = sSQL & ", PM.PM_TQ"
    sSQL = sSQL & ", PM.PM_RD"
    sSQL = sSQL & ", PM.PM_OD"
    sSQL = sSQL & ", PM.PM_CM"
    sSQL = sSQL & ", PM.PM_SP"
    sSQL = sSQL & ", INT(PM.PM_MATL)"
    sSQL = sSQL & ", CHT_Cat"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Pivot DateSerial(Year(CHT_Mon), Month(CHT_Mon), 1)"

    Debug.Print sSQL
    
    With wsDashboard.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
        Application.DisplayAlerts = False
        Intersect(.ResultRange, wsDashboard.Range(wsDashboard.Cells(1, 1), wsDashboard.Cells(1, 15).EntireColumn)).CreateNames _
            True, False, False, False
        Application.DisplayAlerts = True
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnny45]

Thank you for taking the time on this Skip...

I will change my approach accordingly.

Cheers,
Johnny