vba - 在vba中读取另一个文件作为输入

Question

我在 Excel 文件中有这个宏:

Sub ore()
Sheets(1).Select
LR = Cells(Rows.Count, "A").End(xlUp).Row
drow = 2
For r = 2 To LR
  ore = Cells(r, 4)
  nome = Cells(r, 2)
  totore = totore + ore
  n = n + 1
  If ore <> 8 Then
    Rows(r).Copy Sheets("log").Cells(drow, 1)
    drow = drow + 1
  End If
  If n = 5 Then
'    Stop
    If totore <> 40 Then
      Sheets("log").Cells(drow - 1, 5) = totore
    End If
    n = 0: totore = 0
  End If
Next
 Sheets("log").Select
End Sub

当我点击一个按钮时就开始了。该文件名为“example.xlsm”。我想将这个宏写入另一个名为“readfile.xlsm”的文件中，并作为“example.xlsm”文件的输入进行调用。所以我需要汇总读取“example.xlsm”文件的数据。我怎样才能做到这一点？我试着写

Workbooks.Open“C:\Users\Me\Desktop\example.xlsm”

但它不起作用。谢谢

编辑:

Sub Sample()
    Dim path As String
    Dim openWb As Workbook
    Dim openWs As Worksheet

    path = "C:\Users\Me\Desktop\example.xlsm"

    Set openWb = Workbooks.Open(path)
    Set openWs = openWb.Sheets("Sheet1")

    With openWs
        '~~> Rest of your code here
        Sheets(1).Select
        LR = Cells(Rows.Count, "A").End(xlUp).Row
        drow = 2
        For r = 2 To LR
          ore = Cells(r, 4)
          nome = Cells(r, 2)
          totore = totore + ore
          n = n + 1
          If ore <> 8 Then
            Rows(r).Copy Sheets("log").Cells(drow, 1)
            drow = drow + 1
          End If
          If n = 5 Then
        '    Stop
            If totore <> 40 Then
              Sheets("log").Cells(drow - 1, 5) = totore
            End If
            n = 0: totore = 0
          End If
        Next
         Sheets("log").Select
        End With

    'openWb.Close (True)
End Sub

这也不起作用。

Answer 1

您需要创建对象然后使用它们。请参阅此示例。此代码位于 readfile.xlsm

Sub Sample()
    Dim path As String
    Dim openWb As Workbook
    Dim openWs As Worksheet

    path = "C:\Users\Me\Desktop\example.xlsm"

    Set openWb = Workbooks.Open(path)
    Set openWs = openWb.Sheets("Sheet1")

    With openWs
        '~~> Rest of your code here
    End With

    'openWb.Close (True)
End Sub

跟进(来自评论)

当我指的是其余代码时，我并不是说您复制粘贴原始代码而不对其进行任何更改:p还有另一个重要提示:使用Option Explicit 我看到很多未声明的变量。我已将所有这些声明为Long更改(如适用)

试试这个(未经测试)

Option Explicit

Sub Sample()
    Dim path As String
    Dim openWb As Workbook, thiswb As Workbook
    Dim openWs As Worksheet, Logws As Worksheet
    Dim LR As Long, dRow As Long, r As Long, n As Long
    Dim ore As Long, nome As Long, totore As Long

    path = "C:\Users\Me\Desktop\example.xlsm"

    Set thiswb = ThisWorkbook

    Set openWb = Workbooks.Open(path)
    Set openWs = openWb.Sheets("Sheet1")
    Set Logws = openWb.Sheets.Add

    '~~> Create Log Sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    openWb.Sheets("log").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Logws.Name = "log"

    With openWs
        '~~> Rest of your code here
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
        dRow = 2

        For r = 2 To LR
            ore = .Cells(r, 4).Value
            'nome = .Cells(r, 2).Value '<~~ Why do we need this?
            totore = totore + ore

            n = n + 1

            If ore <> 8 Then
                .Rows(r).Copy Logws.Cells(dRow, 1)
                dRow = dRow + 1
            End If

            If n = 5 Then
                If totore <> 40 Then
                    Logws.Cells(dRow - 1, 5) = totore
                End If
                n = 0: totore = 0
            End If
        Next
    End With
    'openWb.Close (True)
End Sub