Bringing data into a pivot table form - Invalid use of ME keyword

Question

I get the error

"Compile Error invalid use of ME keyword"

for the Excel VBA code below. I know from Googling that the cause is because you can't use "ME" in a module, but, I haven't been able to find anything on how to resolve it so it can work in the module.

What I am doing is I'm using VBA and SQL code and hitting a SQL database to bring back some information in pivot table form. But I just don't know how to resolve the ME problem.

Sub RefreshData()
    Dim SQl As String
    Dim startdate As String, Enddate As String
    Dim pvt As PivotTable


    Set pvt = **Me.**PivotTables("PivotTable5")
    pvt.PivotCache.Connection = "ODBC;DSN=" & Application.Range("DSN_Source") & ";DATABASE=" & Application.Range("TD_DB") & ";"

    startdate = Format(**Me**.Range("PSdate"), "yyyy-mm-dd hh:mm:ss")
    Enddate = Format(**Me**.Range("PEdate"), "yyyy-mm-dd hh:mm:ss")

    SQl = ""

    SQl = "WITH PanDetail As " & vbCrLf
    SQl = SQl & "( " & vbCrLf
    SQl = SQl & " SELECT ID, name, SCODE, SDATETime, PDESCRIPTION, ROUND(VOLUME, 5) As VOLUME" & vbCrLf
    SQl = SQl & ", ROUND(MASS, 0) As MASS " & vbCrLf

score 1 · Accepted Answer · answered Mar 30 '20 at 08:00

PivotTables is a member of a Worksheet. You have to tell VBA which sheet you want to use.

The following piece of code would use "Sheet1" of the workbook containing the code. Note the . in front of PivotTables and Range - this tells VBA to use the members of the object of the With-statement.

With ThisWorkbook.Worksheets("Sheet1") 
    Set pvt = .PivotTables("PivotTable5")
    startdate Format(.Range("PSdate"), "yyyy-mm-dd hh:mm:ss")
    Enddate = Format(.Range("PEdate"), "yyyy-mm-dd hh:mm:ss")
    (...)
End With

Alternatives could be

Dim ws as Worksheet
set ws = ThisWorkbook.Sheets("Sheet1") 
Set pvt = ws.PivotTables("PivotTable5")
startdate = Format(ws.Range("PSdate"), "yyyy-mm-dd hh:mm:ss")

or (but messy and harder to change, just for understanding)

Set pvt = ThisWorkbook.Sheets("Sheet1").PivotTables("PivotTable5")
startdate = Format(ThisWorkbook.Sheets("Sheet1").Range("PSdate"), "yyyy-mm-dd hh:mm:ss")

You might benefit from reading https://stackoverflow.com/a/10717999/7599798

Bringing data into a pivot table form - Invalid use of ME keyword

1 Answers1