Editing Connection String in a Boatload of XLSB Documents

Question

I have a couple hundred .xlsb files that need their connection string and command text changed in an easily programmable way. They are all buried in different folders deep in the file system. How can I use Powershell or some other program to go through and edit them all so I don't have to do it manually?

I've started looking into Powershell and Format-Hex. I figured I could ask and someone else may be able to set me on the right track. What needs to be done is recursively searching the filesystem from a certain point, detect if "this string" and this number "11111" are in the connection string and command text (respectively) of all xlsb files, and if they are replace them with "that string" and this number "22222". All in xlsb files. I've also looked into using python, but the libraries I found did not mention editing this setting, so I figured some sort of hex detection and replacement would be easier.

Answer 1

Would it be possible to have more info on what is a "connection string" ? To my knowledge this is not part of the properties of an xlsb file. I suppose it to be the string which is used to create an ODBC Connection so the text you want to modify will be within the code of a macro.

So three issues:

1. Recursively find all xlsb files within a folder

$Fllt = gci "*.xlsb" -r

2. Open them in Excel

$Excl = New-Object -ComObject Excel.Application

$Fllt | %{$xl.Workbooks.Open($_.Fullname)}

3. Replace "this string" by "that string" and "11111" by "22222" in every macro. This is much more difficult.

My suggestion:

#Generation of a test file
$Excl = New-Object -ComObject Excel.Application
$xlve = $Excl.Version
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$xlve\Excel\Security" `
    -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$xlve\Excel\Security" `
    -Name VBAWarnings  -Value 1 -Force | Out-Null
@'
Sub Co()
ConnectionString = "this string"
CommandText = "11111"
End Sub
'@ | Out-File C:\Temp\Test.txt -Encoding ascii
$Wkbk = $Excl.Workbooks.Add()
$Wkbk.VBProject.VBComponents.Import("C:\Temp\Test.txt") | Out-Null
$Wkbk.SaveAs("C:\Temp\Test.xlsb", 50)
$Excl.Quit()

#Get The files
$Fllt = gci -Path C:\Temp\ -Include *.xlsb -r

#Open Excel and set the security parameters to be able to modify macros
$Excl = New-Object -ComObject Excel.Application
$xlve = $Excl.Version
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$xlve\Excel\Security" `
    -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$xlve\Excel\Security" `
    -Name VBAWarnings  -Value 1 -Force | Out-Null


#Loop through the files and modify the macros
$path = "C:\Temp\ModuleVBATemp.txt" #Temp text file to copy and modify the macros 
foreach ($File in $Fllt) {
$Wkbk = $Excl.Workbooks.Open($File.Fullname)
if ($Wkbk.HasVBProject) <# Test if any macro #> {
foreach ($Vbco in $Wkbk.VBProject.VBComponents) {
if ($Vbco.Type -eq '1') <# Only modify the modules #> {
    #Modification of the script
    $Vbco.Export($path) | Out-Null
    (gc $path) -replace "this string","that string" -replace "11111","22222" `
      | Out-File $path -Encoding ascii
    $Wkbk.VBProject.VBComponents.Remove($Vbco)
    $Wkbk.VBProject.VBComponents.Import($path) | Out-Null
}}}
$Wkbk.Close($true) #Save the file
}
$Excl.Quit()

It is working on my test file, I hope that your configuration is similar.