问题

我们有几十个宏启用的excel文件,每个文件都包含少量的VBA模块,在每个模块中都有SQL服务器名称和sql登录的userid /密码.

我想知道我是否可以编写某种C#实用程序,它逐个加载这些文件,或者使用.NET-Office Interport或任何其他方法将这些字符串替换为其他东西...只是因为我必须将所有这些VBA宏重新指向另一个服务器名称并使用另一个sql登录名和密码...我真的不想用手工替换 * (:(

谢谢!

  最佳答案

首先

对不起花一些时间发布,但我正在为它创建一个UI,以便它不仅帮助你,而且帮助任何其他人寻找相同的功能.

您需要首先启用 Trust Access to the VBA project Object model

打开Excel并点击File Tab | Options | Trust Center | Trust Center Settings | Macro Settings

启用宏并单击Trust access to Visual Basic projects

enter image description here

下一个在VBA编辑器

单击工具| Options并在“Editor” Tab下选择复选框Require Variable Declaration

enter image description here

下一个从 下载 Sample 文件 ,并简单地按下 Run Button 在 Sheet1 中启动用户表单,如下所示。

简单地选择只有Excel文件的文件夹.输入相关信息并单击Start Replace并完成:)

enter image description here

使用的代码

Sheet1码区

 Option Explicit

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub
 

userform代码区域

 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
 

更多SNAPSHOTS

enter image description here

在宏运行之前需要替换Whose代码的文件

enter image description here

运行宏后

enter image description here

编辑

替代风险简介

如果上述 wikisend 链接死亡,文件可以从 here 下载。

  相同标签的其他问题

c#.netvbaexcel-vbaoffice-interop