在 vba 中读取另一个文件作为输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19403421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read another file as input in vba
提问by David_D
I have this macro in a excel file:
我在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
That starts when i click a button. This file is called "example.xlsm". I want take this macro and write it in another file called "readfile.xlsm" and call as an input to the "example.xlsm" file. So I need to read the data of "example.xlsm" file in summary. How can I do this? I tried to write
当我点击一个按钮时开始。此文件称为“example.xlsm”。我想把这个宏写在另一个名为“readfile.xlsm”的文件中，并作为“example.xlsm”文件的输入调用。所以我需要总结读取“example.xlsm”文件的数据。我怎样才能做到这一点？我试着写
Workbooks.Open "C:\Users\Me\Desktop\example.xlsm"
Workbooks.Open "C:\Users\Me\Desktop\example.xlsm"
but it doesn't work. Thanks
但它不起作用。谢谢
EDIT:
编辑：
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
This doesn't work either.
这也行不通。
回答by Siddharth Rout
You need to create your object and then work with them. See this example. This code goes in readfile.xlsm
您需要创建对象，然后使用它们。请参阅此示例。这段代码进去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
FOLLOWUP (From Comments)
跟进（来自评论）
When I meant rest of the code, I didn't mean that you copy paste the original code and not make any changes to it :p Also another important tip: Use Option ExplicitI see lot of undeclared variables. I have declared all of them to LongChange as applicable
当我的意思是rest of the code，我并不是说您复制粘贴原始代码而不对其进行任何更改：p 还有另一个重要提示：使用Option Explicit我看到很多未声明的变量。我已宣布所有这些Long更改适用
Try this (Untested)
试试这个（未经测试）
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
