Save ODC files using VBA

Greetings, all. First timer.

Note: this was originally posted on the MrExcel forum (unable to post link as newbie), but no responses have been received in one week.

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:

Please Login or Register  to view this content.

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 in line 70: 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.

Guessing, based on the fact error 438 is 'Object does not support this property or method' (or words to that effect).

Please Login or Register  to view this content.

Sorry for the delay cytop, and many thanks.

Your suggestions put me in the trail of the final solution: line 70 should be [ .OLEDBConnection.SaveAsODC newSrcConnFN ( no reference to the path)

This formulation worked. Thanks for your help.