VBA Macro to refresh ODBC connection only works once

[orbitmantiago]

Hello all,

I'm trying to use a macro to refresh a pivot table obtained thru an ODBC connection. At the same time, i want to change the CommandText field to change the dates i'm using (I only want to get one year of data). I did this by recording the refresh and then changing the CommandText. Here is the code I have at the moment:

    FullString = "V_DATE_ACT>={ts '" + Dinicio + " 00:00:00'}) AND (V_LOGONE_DB.DLV_DATE_ACT<{ts '" + Dfim + " 00:00:00'})"
 
 
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "MATERIAL_CODE_ACT[All]", _
        xlLabelOnly, True
    With ActiveWorkbook.Connections("Connection").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array( _
        "SELECT V_LOGONE_DB.EAN_DESC, V_LOGONE_DB.EAN_ITEM, V_LOGONE_DB.EAN_ORD, V_LOGONE_DB.MATERIAL_CODE_ACT, V_LOGONE_DB." _
        , _
        "EAN_QTY_INV, V_LOGONE_DB.DLV_DATE_MONTH" & Chr(13) & "" & Chr(10) & "FROM CIOR.V_LOGONE_DB V_LOGONE_DB" & Chr(13) & "" & Chr(10) & "WHERE (V_LOGONE_DB.CLIENT_NO=56) AND (V" _
        , _
        "_LOGONE_DB.BUS_CAT_NO=1) AND (V_LOGONE_DB.CDN_TYPE='D') AND (V_LOGONE_DB.SHIPMENT_STATUS='S41') AND (V_LOGONE_DB.DL" _
        , _
        FullString _
        )
        .CommandType = xlCmdSql
        .Connection = _
        "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=bv7728;;SERVER=ciorp101.eu.pg.com;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        .Refresh
    End With
    With ActiveWorkbook.Connections("Connection")
        .Name = "Connection"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Connection").Refresh

The variables Dinicio and Dfim represent the start and end dates i'm looking to put inside the commandtext. They're written as yyyy-mm-dd as were the ones in the original record text.

The weird thing is that this refresh only works the first time i run it, and the same happens to the one I recorded (with no string changes). I record and it refreshes while recording, then i run it again and it refreshes as well but on every subsequent attempt afterwards it does nothing. The code doesn't even provide any error, it just keeps running and does nothing, not even if the dates are manually changed to be diferent from the ones provided in the macro.

Can anyone help?

Thanks

 

[orbitmantiago]

Found the solution to my own problem, but posting here for anyone that runs into the same issue.

What happens is that, for some reason, whenever excel refreshes the connection through VBA it creates a new connection, with a diferent name. Therefore, when running the macro again the name is diferent and the relevant connection is not refreshed or changed. The workaround was changing the line

With ActiveWorkbook.Connections("Connection").ODBCConnection

for

With ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).ODBCConnection

This refreshes the last connection, as excel names them ConnectionN where N starts at 1 and keep growing.

Hope this can help someone out there.

P.S

Ended up simplifiying the code and reducing it to

FullString = "(V_LOGONE_DB.DLV_DATE_ACT>={ts '" + Dinicio + " 00:00:00'}) AND (V_LOGONE_DB.DLV_DATE_ACT<{ts '" + Dfim + " 00:00:00'})"
    
With ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).ODBCConnection
    .CommandText = "SELECT V_LOGONE_DB.EAN_DESC, V_LOGONE_DB.EAN_ITEM, V_LOGONE_DB.EAN_ORD, V_LOGONE_DB.MATERIAL_CODE_ACT, V_LOGONE_DB.EAN_QTY_INV, V_LOGONE_DB.DLV_DATE_MONTH" & Chr(13) & "" & Chr(10) & "FROM CIOR.V_LOGONE_DB V_LOGONE_DB" & Chr(13) & "" & Chr(10) & "WHERE (V_LOGONE_DB.CLIENT_NO=56) AND (V_LOGONE_DB.BUS_CAT_NO=1) AND (V_LOGONE_DB.CDN_TYPE='D') AND (V_LOGONE_DB.SHIPMENT_STATUS='S41') AND " + FullString
End With

 

[LeonK1982]

Very many thanks Orbitmantiago - Four years on and your answer is still helping people. I was pulling my hair out for a day trying to solve this issue.