时间戳

首页 > 解决方案 > 如何从打开的文件中获取数据？

excel - 如何从打开的文件中获取数据？

问题描述

我有这段代码，

工作表：“主要”B3：我在文件中搜索的内容。
工作表“AX”：在哪里搜索。

我想在另一个以字母开头的打开的工作簿中搜索：ECL_

Sub FindandKopy()
Dim CellContents As String
Dim rng As Range
Dim loDeinWert As String
Dim sfirstaddress As String

loDeinWert = Worksheets("main").Range("B3").Value

Set rng = Worksheets("AX").Range("B:B").Find(loDeinWert)
If rng Is Nothing Then
  MsgBox "Data " & loDeinWert & " not found!"
Else
  sfirstaddress = rng.Address
  Do
    rng.EntireRow.Copy
    Worksheets("Main").Cells(Rows.Count, "A").End(xlUp) _
      .Offset(6, 0).PasteSpecial Paste:=xlPasteAll 
    Set rng = Worksheets("AX").Range("B:B").FindNext(rng)
  Loop While Not rng Is Nothing And rng.Address <> sfirstaddress
End If
End Sub

标签： excelvba

解决方案

如果您需要查找已经打开的工作簿，您可以遍历所有工作簿并检查每个工作簿名称：

Dim ECLWb As Workbook
Dim wb As Workbook
For Each wb in Application.Workbooks
    If wb.Name Like "ECL_*" Then 
        Set ECLWb = wb
        Exit For
    End If
Next wb

If ECLWb Is Nothing Then
    MsgBox "ECL_* not found."
    Exit Sub
Else
    'use that workbook like 
    ECLWb.Worksheets("AX")…
End If

推荐阅读