从另一个Excel返回行值 [英] Return Row Value from another Excel

问题描述

我需要通过在3-4个Excel文件中搜索一个字符串来返回一个值.值应该是与我的搜索字符串匹配的行的行数据.我在atm遇到的问题是我只是无法获取Cell或Row的值.

I need to return a Value by searching for a String in 3-4 Excel Files. The Value should be the row Data of the row where it matches my searchstring. The Problem that I am having atm is that I just cant get the value of the Cell or Row.

工作原理:

1. UserForm有一个文本框，用户可以在其中输入字符串
2. UserForm有一个按钮-应该使用字符串onPress并检查Excelfiles是否存在该字符串
3. 如果在Excel文件中找到该字符串，那么我想获取该行中的值
4. 如果我随后有了所需的值，我想将它们保存在活动Excel中，从中将其称为此宏.

我尝试了2种方法:

1.

Sub ReadDataFromAnotherWorkBook()

    Dim SearchString As String
    Dim SearchRange As Range, cl As Range
    Dim FirstFound As String
    Dim sh As Worksheet

    ' Open Workbook A with specific location
    Dim src As Workbook
    Set src = Workbooks.Open("C:\Users\xx\Desktop\xx\test\xx304..xlsx", True, True)      
    
        ' Set Search value
    SearchString = TextBox1.Value                                   ' TEST with TextBox Value -- works
    Application.FindFormat.Clear
    ' loop through all sheets
    For Each sh In src.Worksheets
        ' Find first instance on sheet
        Set cl = sh.Cells.Find(what:=SearchString, _
            After:=sh.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not cl Is Nothing Then
            ' if found, remember location
            FirstFound = cl.Address
            MsgBox FirstFound
            ' format found cell
            Do
              '  cl.Font.Bold = True
              '  cl.Interior.ColorIndex = 3
              Debug.Print FirstFound
              MsgBox FirstFound                                             ' does not work
              
'              Debug.Print cl.Value
'              MsgBox cl.Value                                            ' I expected the value here  :/ 
                ' find next instance
                Set cl = sh.Cells.FindNext(After:=cl)
                ' repeat until back where we started
            Loop Until FirstFound = cl.Address
        End If
    Next
    
    MsgBox "Value im Excel? : " + FirstFound                               'cl.Value            > the return is empty.. 


    MsgBox "SEARCHSTRING :: " + SearchString                               ' works - I have the searchstring I need

    ' Close Workbooks A                                                    ' closes the wb
    src.Close False
    Set src = Nothing

End Sub

我的第二种方法是这样的:

my 2nd approach was something like this:

Sub ReadDataFromAnotherWorkBookTEST2()                                           ' 2nd approach

' Open Workbook A with specific location
Dim src As Workbook
Set src = Workbooks.Open("C:\Users\x\Desktop\x\test\x304..xlsx", True, True)
    
Dim oFound As Range
Dim oLookin As Range

Dim test As Worksheet

Dim sLookFor As String

sLookFor = TextBox1.Value                 ' TextBoxValue

MsgBox "SearchString " + sLookFor         ' works got the right string 

Set oLookin = src.Worksheets("Tabelle1").UsedRange       

' Set test = src.Worksheets("Tabelle1")     ' Find geht nicht auf WorkSheet

Set oFound = oLookin.Find(what:=sLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)


Dim oAdjacent As Range

If Not oFound Is Nothing Then
     Debug.Print "Found? : " & oFound.Address
     MsgBox "this Box does not spawn..  " + oLookin.Range("A" & oFound.Row).Value
 '   ActiveCell.Value = oLookin.Range("A" & oFound.Row).Value
 '   Set oAdjacent = oFound.Offset(0, 1) ' One cell to right
    
End If

    ' Close Workbooks A                                                    ' Closes the Workbook
    src.Close False
    Set src = Nothing

End Sub

有些事情我做错了，但我不知道是什么原因..我是VBA初学者，所以也许你们中的某人可以为我提供一些指导.

There is something that I am doing wrong but I cant figure out what it is.. I am a VBA beginner, so maybe someone of you can guide me a bit.

推荐答案

在多个工作表中查找字符串的多次出现

Option Explicit

Sub ReadDataFromAnotherWorkBook()

    Dim swb As Workbook
    Set swb = Workbooks.Open("C:\Users\xx\Desktop\xx\test\xx304..xlsx", True, True)
    Debug.Print "Processing workbook '" & swb.Name & "'..."
    
    Dim sString As String: sString = TextBox1.Value
    Debug.Print "Searching for '" & sString & "'..."
    
    Dim sws As Worksheet
    Dim srg As Range
    Dim sCell As Range
    Dim fAddress As String
    
    For Each sws In swb.Worksheets
        Debug.Print "Processing worksheet '" & sws.Name & "'..."
        Set srg = sws.UsedRange
        Set sCell = srg.Find(What:=sString, _
            After:=srg.Cells(srg.Rows.Count, srg.Columns.Count), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows)
        If Not sCell Is Nothing Then
            fAddress = sCell.Address
            Do
                Debug.Print "Found string in '" & sCell.Address(0, 0) _
                    & "'. The value in " & sCell.Offset(, 1).Address(0, 0) _
                    & " is " & sCell.Offset(, 1).Value
                Set sCell = srg.FindNext(sCell)
            Loop Until sCell.Address = fAddress
        End If
    Next
    swb.Close False

End Sub

这篇关于从另一个Excel返回行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！