Need help fixing macro that changed ODBC connection string - was VisualFoxPro now SQL

[9tanstaafl9]

My reports used to connect to our database using a VisualFoxPro ODBC driver, but the new version of the database requires an SQL connection. I have been able to connect just fine, my problem is that I need to modify the macro that lets me change the database path when the report is given to other users.

Unfortunately, while I am passable at writing macros, I don't know anything about connections, and I don't understand the code that some nice person gave to me several years ago. I just know it worked.

The code used to be:

Sub ChangeDatabasePathManually()
'changes all queries in workbook 
 
Const strPath As String = "c:\FOLDERNAME\SUBFOLDERNAME"

  Dim qt As QueryTable
  Dim wks As Worksheet
 
  For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
      With qt
 
        .Connection = Join$(Array( _
            "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
            strPath, _
            ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
            ), vbNullString)
 
      End With
    Next qt
  Next wks
 
  Set qt = Nothing
  Set wks = Nothing
End Sub

If I copy and paste the connection string from within Excel, the one that worked with the above, it looks like this:

DSN=Visual FoxPro Tables;UID=;SourceDB=c:\FOLDERNAME\SUBFOLDERNAME;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

The new connection string looks like this:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=CATALOGNAME;Data Source=COMPUTERNAME\PROGRAMNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=COMPUTERNAME;Use Encryption for Data=False;Tag with column collation when possible=False

I tried changing the previous macro to sub in the new information, but when I run it nothing seems to happen. I tried stepping through, and it looks like Excel doesn't think there are any query tables present. Are SQL queries not querytables? Maybe that's the problem?

Sub ChangeDatabasePathManuallySQL()

Const initialCatalog As String = "CATALOGNAME"
Const dataSource As String = "COMPUTERNAME\PROGRAMNAME"
Const workstationID As String = "COMPUTERNAME"

  Dim qt As QueryTable
  Dim wks As Worksheet
 
  For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
      With qt
 
        .Connection = Join$(Array( _
            "ODBC;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=", _
            initialCatalog, _
            ";Data Source=", _
            dataSource, _
            ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=", _
            workstationID, _
            ";Use Encryption for Data=False;Tag with column collation when possible=False" _
            ), vbNullString)
 
      End With
    Next qt
  Next wks
 
  Set qt = Nothing
  Set wks = Nothing
End Sub

Any help at all is greatly appreciated. Even if you don't have time to fix it, if you could point me in the right direction I would be greatful. At the very least I'd like to know if I just did something in my blind attempt to modify the code, or if this just isn't something that is possible to do. IE, did I put a comma in the wrong place, or is SQL not even an ODBC connection type? I really no NOTHING about connections. Someone else does that part and hands it off to me to do the macros.


Thank you!!!!!!!!

 

[9tanstaafl9]

Actually, I probably phrased this wrong. My point is that I got the data I need into Excel just fine, but I need to make the workbook transferrable for others on an entirely different network to use and I have no idea how to do that. If anyone could help, or tell me where to go for help or who to call I would be greatful. We don't need to "fix" my macro, we could use an entirely different one, or if there is some other way to make it transferrable? I have an old report I gave to people and they rely on it, and I need to make a new version for them. But it won't do me any good to create the new report if I have no way to give it to them.