Can the database name be easily changed from all ODBC queries in

Markku_E This can be done with VBA, but the level of difficulty will depend on a few things...

• Is the database (source) the same for each ODBC connection in the workbook?
• What type of ODBC connection is it (ie: SQL Server, MS Access Database, Excel Files, etc.)?
• Is the connection string the same for each ODBC connection in the workbook?

The first thing you might want to do is generate a list all ODBC connections in the workbook with their connection strings. To do this, try running the following code, which will output the list to the Immediate Window:

Sub PrintOdbcConnections()
    Dim Item As WorkbookConnection
    For Each Item In ActiveWorkbook.Connections
        If Item.Type = xlConnectionTypeODBC Then
            Debug.Print Item.Name & " | " & Item.ODBCConnection.Connection
        End If
    Next Item
End Sub

Note: to display the Immediate Window in Visual Basic, go to View > Immediate Window (or press Ctrl+G).

If ALL of the connection strings are the same for each ODBC connection in the workbook, the following code can be used to update each one by specifying a new connection string:

Sub UpdateOdbcConnections()
    Dim Item As WorkbookConnection, newStr As String
    newStr = "type the complete new connection string here"
    For Each Item In ActiveWorkbook.Connections
        If Item.Type = xlConnectionTypeODBC Then
            Item.ODBCConnection.Connection = newStr
        End If
    Next Item
    ActiveWorkbook.RefreshAll
End Sub

If they are not all the same, however, further code would need to be written to identify the differences and update each one accordingly. DO NOT run the above code without first confirming that ALL connection strings are the same. This is a very basic example only, intended to be used as a guide or starting point. Without knowing the specifics of your situation, this is as far as I can go at this time.

Some examples of what the connection strings might look like include:

'SQL Server:
    newStr = "ODBC;DRIVER=SQL Server;SERVER=EXAMPLE;UID=admin;PWD=password;APP=Microsoft Office 2010;WSID=PC1"

'MS Access Database:
    newStr = "ODBC;DSN=MS Access Database;DBQ=C:\Database\Test.accdb;DefaultDir=C:\Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

'Excel Files:
    newStr = "ODBC;DSN=Excel Files;DBQ=C:\Users\User\Desktop\Test.xlsx;DefaultDir=C:\Users\User\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Markku_E 

there are several ways to change the database name for all your ODBC queries in Excel without manually editing each one. Here are three options:

1. Modify connection strings:

• This method involves editing the connection strings directly within your ODBC queries. Each query likely contains a connection string referencing your old database name.
• You can locate these strings by searching for keywords like "DSN", "SERVER", or "DATABASE" within your queries.
• Once identified, simply replace the old database name with the new one.
• While effective, this approach can be tedious for a large number of queries.

2. Use a shared parameter file:

• This method involves storing the database name in a separate file, like a text file (.txt) or Excel sheet, and accessing it within your queries.
• Create a dedicated cell or line in your file to store the database name.
• In your ODBC queries, use a function like TEXTQUERY or SUBSTITUTE to dynamically reference and replace the placeholder for the database name with the value from your shared file.
• This approach allows you to easily update the database name in one place, and the change will automatically reflect in all your queries.

3. Leverage VBA macros:

• This method involves writing a macro in VBA (Visual Basic for Applications) to automate the process of updating the database name across all your queries.
• The macro can loop through each query, parse the connection string, and replace the old database name with the new one.
• While requiring some coding knowledge, this approach offers the most flexibility and automation for complex scenarios.

Additional tips:

• Before making any changes, backup your original spreadsheet and queries.
• Test your queries after updating the database name to ensure they function correctly.
• Consider using descriptive variable names for the database name in your shared file or macros for better clarity.

Choose the method that best suits your comfort level and technical expertise.

I hope the information above has been helpful. Please consider remembering me in your prayers. May peace be upon you