时间戳

首页 > 解决方案 > Excel宏替换多个excel文件中的字符串

excel - Excel宏替换多个excel文件中的字符串

问题描述

我正在尝试创建一个 VB 宏来替换在目录中的多个 excel 文件中找到的字符串。我的代码在下面，但它不起作用，我不确定我需要做什么来修复它。

有什么建议么 ？

Sub ReplaceStringInExcelFiles()

Dim MyFile As String
Dim FilePath As String
Dim orig As String
Dim news As String

orig = "cow"
news = "dog"

FilePath = "C:\myDir\"
MyFile = Dir(FilePath)


Do While Len(MyFile) > 0

Workbooks.Open (FilePath & MyFile)
For q = 1 To Application.Worksheets.Count
Worksheets(q).Activate
Sheets("Sheet1").Cells.Replace what:=Original_String, Replacement:=New_Replacement_String, lookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next q
ActiveWorkbook.Save
ActiveWorkbook.Close
MyFile = Dir
Loop


End Sub

标签： excelvba

解决方案

尝试这个：

Sub ReplaceStringInExcelFiles()

    Dim MyFile As String
    Dim FilePath As String
    Dim orig As String
    Dim news As String
    Dim wb As Workbook, ws As Worksheet

    orig = "cow"
    news = "dog"

    FilePath = "C:\myDir\"
    MyFile = Dir(FilePath & "*.xls*")


    Do While Len(MyFile) > 0
        Set wb = Workbooks.Open(FilePath & MyFile) '<< assign the workbook to wb
        'Loop over the worksheets
        'Note: no need to activate/select
        For Each ws In wb.Worksheets
            ws.UsedRange.Cells.Replace what:=orig, _
                            Replacement:=news, _
                            lookAt:=xlPart, SearchOrder:=xlByRows, _
                            MatchCase:=False
        Next ws
        ActiveWorkbook.Close savechanges:=True
        MyFile = Dir
    Loop

End Sub

推荐阅读