用VBA替换引号

Question

我正在从Access迁移到SQL Server。有Excel工作簿连接到Access DB与ADO。它们并不都是相等的，所以不可能替换模块。

我正在寻找并替换连接。

我想替换里面有引号引用另一个声明变量的字符串。
例如替换VBA字符串
"Data Source= ",路径,Filename
with
Data Source= ",Data_source,初始目录= ",Initial_catalog

我想用@替换字符串中的引号，然后再回到"，但这对于FIND WHAT是不可能的，正如你在下面看到的那样，而且效率不高。我在另一个宏中定义数据源和初始目录。

完整代码:

Sub ReplaceTextInCodeModules()
' Must add a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
' Also must set "Trust access to the VBA project object model"
' See the url below for more info on these.
' Based on code found at:
' Source: www.cpearson.com/excel/vbe.aspx Copyright 2013, Charles H. Pearson
Dim theWorkbook As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim lineNum As Long
Dim thisLine As String
Dim message As String
Dim numFound As Long
Const FIND_WHAT1 As String = "Provider=Microsoft.ACE.OLEDB.12.0; "
Const REPLACE_WITH1 As String = "Provider=SQLOLEDB;"

Const FIND_WHAT2 As String = "@Data Source= @ & Path & filename & @;@"
Const REPLACE_WITH2 As String = "@Data Source= @ & Data_source & @ Initial Catalog= @ & Initial_catalog & @ Integrated Security=SSPI; @"
numFound = 0
For Each theWorkbook In Application.Workbooks
If theWorkbook.Name <> ThisWorkbook.Name Then
If theWorkbook.HasVBProject Then
Set VBProj = theWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
'Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule
With CodeMod
numLines = .CountOfLines
For lineNum = 1 To numLines
thisLine = .Lines(lineNum, 1)
If InStr(1, thisLine, FIND_WHAT1, vbTextCompare) > 0 Then
message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
.ReplaceLine lineNum, Replace(thisLine, FIND_WHAT1, REPLACE_WITH1, , , vbTextCompare)
numFound = numFound + 1
End If
If InStr(1, thisLine, FIND_WHAT2, vbTextCompare) > 0 Then
message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
.ReplaceLine lineNum, Replace(thisLine, FIND_WHAT2, REPLACE_WITH2, , , vbTextCompare)
numFound = numFound + 1
End If

Next lineNum
End With
Next VBComp
End If
End If
Next theWorkbook
Debug.Print "Found: " & numFound
If message <> "" Then
Debug.Print message
End If
If numFound = 0 Then
MsgBox ("Nothing found to replace.")
Else
MsgBox ("Paths replaced!" & vbNewLine & message)
End If
End Sub

Answer 1

你没有试着澄清我评论中的问题…

在我的国家已经很晚了，我会关上我的笔记本电脑。请参阅下面的测试代码，该代码展示了如何处理要替换的字符串，对于我在注释中处理的两种情况:

Dim x As String, y As String, z As String, w As String
Dim xx As String, yy As String

x = "Data Source=  & Path & Filename"
y = "Data Source=  & Data_source & ""Initial Catalog= "" & Initial_catalog"
z = """Data Source=""  & Path & Filename"
w = """Data Source=""  & Data_source & ""Initial Catalog= "" & Initial_catalog"
Debug.Print x 'how the strings looks in the code lines:
Debug.Print y
Debug.Print z
Debug.Print w
Debug.Print
xx = "abcd xyz " & x & " 12345678"
Debug.Print "Replace1 = " & Replace(xx, x, y) 'the string containing the replacement
yy = "xxxxwwww45 " & z & " aaaabbbb"
Debug.Print "Replace2 = " & Replace(yy, z, w) 'the string containing the replacement

Answer 2

谢谢FaneDuru，

成功了，三句话成功了。我的目标是替换cn。公开声明。我甚至通过将输出写入工作表上的列表框来使它更花哨:

If InStr(1, thisLine, FIND_WHAT, vbTextCompare) > 0 Then
message = theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & " | Connection1"
ThisWorkbook.Sheets("Replace").ListBox1.AddItem message
.ReplaceLine lineNum, Replace(thisLine, FIND_WHAT, REPLACE_WITH, , , vbTextCompare)
numFound = numFound + 1
End If