Text replaces VBA code for Excel files

Question

we have dozens of excel macros, each of which contains several VBA modules, and each of these modules has SQL server names and user IDs / passwords for logging into sql.

I wonder if I can write some C # utility that downloads these files one at a time and either from .NET-Office Interop. or any other approach replaces these lines with something else ... just because I have to reassign all these VBA macros to a different server name and use a different username and password to log into sql ... I really would not want to do this replacement arm :(:(: (

thanks!

Answer 1

To start with

Sorry for taking some time in the publication, but I created a user interface so that it not only helps you, but everyone else who is looking for the same functionality.

You need to enable Trust Access to the VBA project Object model

Open Excel and click on the "File" tab | Options | Trust Center | Trust Center Settings | Macro settings

Turn on the macro and click Trust access to Visual Basic projects

Next In the VBA Editor

Click "Tool" | Options and Editor tab, check the Require Variable Declaration box.

Next Download the sample file from here and just click the Run button in Sheet1 to start the custom form as shown below.

Just select the folder in which there are ONLY Excel files. Enter the appropriate information and click Start Replace , and everything will be ready :)

Code used

Code Area Sheet1

 Option Explicit Private Sub CommandButton1_Click() UserForm1.Show End Sub 

User Code Area

 Option Explicit Private Sub CommandButton1_Click() Dim Ret Ret = BrowseForFolder If Ret = False Then Exit Sub TextBox1.Text = Ret End Sub Private Sub CommandButton3_Click() On Error GoTo Whoa Dim wb As Workbook Dim strPath As String, strfile As String Dim strToReplaceWith As String, strToReplace As String Dim i As Long, j As Long Dim VBE As Object strPath = TextBox1.Text & "\" strfile = Dir(strPath) While strfile <> "" Set wb = Workbooks.Open(strPath & strfile) Set VBE = ActiveWorkbook.VBProject If VBE.VBComponents.Item(1).Properties("HasPassword").Value = False Then If VBE.VBComponents.Count > 0 Then For i = 1 To VBE.VBComponents.Count VBE.VBComponents.Item(i).Activate If VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines > 0 Then For j = 1 To VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines If InStr(1, VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1), TextBox2.Text, vbTextCompare) Then strToReplace = VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1) strToReplaceWith = Replace(strToReplace, TextBox2.Text, TextBox3.Text, 1, 1, vbTextCompare) VBE.VBE.CodePanes.Item(i).CodeModule.ReplaceLine j, strToReplaceWith End If Next End If Next i End If End If wb.Close True strfile = Dir Wend LetsContinue: Application.ScreenUpdating = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub '~~> Function to pop the browse folder dialog Function BrowseForFolder(Optional OpenAt As Variant) As Variant Dim ShellApp As Object '~~> Create a file browser window at the default folder Set ShellApp = CreateObject("Shell.Application"). _ BrowseForFolder(0, "Please choose a folder", 0, OpenAt) '~~> Set the folder to that selected. (On error in case cancelled) On Error Resume Next BrowseForFolder = ShellApp.self.Path On Error GoTo 0 '~~> Destroy the Shell Application Set ShellApp = Nothing Select Case Mid(BrowseForFolder, 2, 1) Case Is = ":" If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid Case Is = "\" If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid Case Else GoTo Invalid End Select Exit Function Invalid: BrowseForFolder = False End Function Private Sub CommandButton4_Click() Unload Me End Sub 

MORE SNAPSHOTS

The file whose code must be replaced before running the macro

After running the macro

EDIT

ALTERNATIVE FILE DOWNLOAD LOCATION

If the above wikisend link freezes, the file can be downloaded from here

Answer 2

I suggest you create a configuration file containing the names and credentials of your server. Then you add a module to each of your Excel files that analyzes this configuration file at startup and populates it with global variables. You just need to configure the variables for the server name, etc. In all your VBA modules for new global variables.

Thus, you can change your access data at any time convenient for you by editing or replacing a text file.

Answer 3

I suggest this way to solve your problem. You can create a separate vba or vbscript project that will load all spreadsheets one by one, exporting the contents of your vba projects into separate text files. You can then download these text files and perform line replacements. After that, you can import the text files back into the spreadsheet as components of the vba project (by simply returning the export process).

You can use this code, like this one, to export components:

 Public Sub ExportAppSrcs(targetWb as Workbook) Dim wb As Workbook, Component As Object, Suffix As String, fileName As String Set wb = targetWb For Each Component In wb.VBProject.VBComponents Select Case Component.Type Case 1 'modul Suffix = ".bas" Case 2 'class modul Suffix = ".cls" Case 3 'form Suffix = ".frm" Case 100 'dokument Suffix = ".xwk" Case Else Suffix = "" End Select If Suffix <> "" Then On Error Resume Next fileName = wb.Path & "\spreadsheet.xlsm.src\" & Component.name & Suffix Component.Export fileName End If Next End Sub