VBA Macro to refresh ODBC connection only works once

orbitmantiago

New Member
Joined
May 11, 2010
Messages
4
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:

Code:
    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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

orbitmantiago

New Member
Joined
May 11, 2010
Messages
4
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

Code:
With ActiveWorkbook.Connections("Connection").ODBCConnection
for
Code:
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

Code:
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

New Member
Joined
Jul 23, 2014
Messages
1
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,208
Messages
5,857,918
Members
431,907
Latest member
RNN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top