Mass update of connection string to Excel file

Question

We recently changed our SQL database server, and I was wondering if there is a script or an easier way to update all the connection strings of Excel files?

It would be much easier if they used the connection file, but, unfortunately, they were all installed manually, and we have about 600 reports ...

Any help is greatly appreciated.

thanks

Nick

Answer 1

Yes, you can ... you create a program in C # or vb.net that processes all your 600 documents and opens documents and using

oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule) oModule.CodeModule.AddFromString(sCode) 

and depending on your setting in the sCode variable, you have a macro that cycles through Excel. Connections or

  For Each wks In ActiveWorkbook.Worksheets For Each qt In wks.QueryTables With qt .Connection ="myconnstring" End With Next qt Next wks 

Answer 2

I wanted to do the same and came through this tool called XLODCTool from here .

Link to the file here .

Provide you with volumetric changes in the values ​​inside the connection string, for example.

DSN From SERVERA to SERVERB

Answer 3

Based on the Archlight solution, the macro looks like this:

 Sub UpdateConnectionsString_Click() For Each wks In ActiveWorkbook.Worksheets For Each qt In wks.QueryTables With qt 'Debug.Print .Connection .Connection = Replace(.Connection, "bla.com", "localhost") End With Next qt Next wks End Sub