我如何在excel文件中查找和替换 [英] how do i do find and replace in excel file

问题描述

我的代码在这里我阅读了所有单元格文本，但我不知道在Excel中查找和重新获取的方法。

My code is here i read all cell text but i don't known method for find and reaplce in excel.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim oExel As Excel.Application
    Dim oWorkbook As Excel.Workbook
    Dim oWorksheet As Excel.Worksheet
    Dim oRange As Excel.Range
    Dim rCnt As Integer
    Dim cCnt As Integer
    Dim Obj As Object
    Dim Obj1 As Object
    Dim sReplace As String = "ABC"
    oExel = CreateObject("Excel.Application")
    oWorkbook = oExel.Application.Workbooks.Open(TextBox1.Text)
    oExel.Application.Interactive = True
    oExel.Application.UserControl = True
    For Each oWorksheet In oExel.ActiveWorkbook.Worksheets
        oRange = oWorksheet.UsedRange
        For rCnt = 1 To oRange.Rows.Count
            For cCnt = 1 To oRange.Columns.Count
                Obj = CType(oRange.Cells(rCnt, cCnt), Excel.Range).Text
                If Obj <> Nothing Then
                    ' find and replace
                    'MessageBox.Show(Obj)
                End If
            
            Next
        Next
    Next
    oWorkbook.Save()
    oWorkbook.Close()
    oExel.Quit()
    oExel = Nothing
End Sub

请帮帮我解决这个问题。在此先感谢

please help me for solving this. Thanks in advance

推荐答案

我没有安装VB但是如果我想给你一个在中实现操作的一般线索Interop 方式首先开始在excel中录制宏，然后完成所需的工作，然后停止录制。



然后去查看>宏，然后编辑新录制的宏。您应该调用在宏中安排的确切方法和参数。



例如，对于替换我们应该添加对Microsoft.Interop.Excel的引用然后有像这样的代码：

I don't have VB installed but If I want to give you a general clue for implementing an operation in Interop manner first start recording a macro in excel and then do your desired job and then stop recording.

Then go to view > macros and then edit the newly recorded macro. You should call the exact method and parameters as they are arranged in that macro.

For example for replacing we should add a reference to Microsoft.Interop.Excel and then have a code like this :

var oExel = new Excel.Application { DisplayAlerts = false };
oExel.Workbooks.Open(@"C:\abc.xlsx");

oExel.Application.Interactive = true;
oExel.Application.UserControl = true;
oExel.Visible = true;
oExel.Cells.Replace(What: "rain", Replacement: "snow", LookAt: XlLookAt.xlPart,
                    SearchOrder: XlSearchOrder.xlByRows
                    , MatchCase: false, SearchFormat: false, ReplaceFormat: false);

此代码在C＃中，它是从记录的宏转换而来的，它们几乎完全相同（替换部分我的意思是。

可能如果您复制并粘贴该宏，则不需要进行任何更改，因为您使用VB语言开发。





希望它有助于

This code is in C# and It was converted from a macro which was recorded and they are almost exactly the same (replacement part I mean).
May be If you copy and paste that macro you do not need any change because you develop in VB language.


Hope It Helps

这篇关于我如何在excel文件中查找和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！