Rename and Save ODC files using VBA

[eriel_ramos]

Greetings, all. First timer.

I'm running models in Excel for MS365 which use from 5 to 15 Power Query queries from both external sources and from in-worksheet tables.

Due to the nature of the data sources, each month I have to do fine-tuning of the queries, so a query named [proKPI_Owners] last month is not the necessarily the same as [proKPI_Owners] this month.

After I finish editing the queries, I got through all the queries, right-click and select {Export Connection File} to backup. Works perfectly.

The challenge:
I would like to set up a VBA subroutine to:

1. cycle through all the workbook's connections,
2. edit the connection name with a current-date suffix (Query - proKPI_Owners 2022-06-14), and
3. save the updated connection file as ODC.

My current effort is as follows:

Sub BackupConnections()
    Dim wb As Workbook:             Set wb = ActiveWorkbook
    Dim ix As Integer:              ix = 0
    Dim obConn As Variant, newSrcConnFN As String
    Dim ODC_Save_Path As String
    ODC_Save_Path = "Z:\IT Dept\BTS G&S\40 Governance\~Operational Activities\aa All Query files\"

    For Each obConn In ThisWorkbook.Connections
        If obConn.Name <> "" Then
10:        ix = ix + 1
'20:        On Error Resume Next
25:        On Error GoTo ErrorHandler

30:        With obConn
40:            newSrcConnFN = .Name & Format(Now(), " yyyy-mm-dd") & ".odc"
            
50:            If Left(newSrcConnFN, 5) = "Query" Then
55:                SrcConnFileOLE = .OLEDBConnection.SourceConnectionFile

60:                Debug.Print ix & "| " & SrcConnFileOLE & "/" & (ODC_Save_Path & newSrcConnFN)

70:                obConn.SaveAsODC (ODC_Save_Path & newSrcConnFN)
80:             End If      
90:        End With
        End If
    Next
'------------------------------------------
    Exit Sub

' Print detailed error message and resume next statement
ErrorHandler:
    msg = "Conn. #" & ix & " | Error # " & str(Err.Number) & " was generated at Line: " & Erl
    
    Debug.Print msg
    Resume Next
'------------------------------------------
End Sub

The subroutine cycles as expected through all the queries, retrieves the needed query parameters, and generates the expected new ODC filename.

My problem is the SaveAsODC command: when it runs for any of the queries, I get "Conn. #1 | Error # 438 was generated at Line: 70"

I've searched through MS documentation and as many forums as I can find, and cannot find any example of the correct use of this command to programmatically save ODC files.

I would greatly appreciate any assistance.

 

[eriel_ramos]

Solution found.

line 70 should be [ .OLEDBConnection.SaveAsODC newSrcConnFN ( no reference to the path)

Hope this helps.