vba 引用新打开的工作簿以在其上运行代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42196413/
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
Reference a newly opened Workbook to run code on it
提问by JjKaf
I know I can open a workbook and have code run in the same workbook if the mapping to the file is pasted into the macro code.
我知道如果将文件映射粘贴到宏代码中，我可以打开工作簿并在同一工作簿中运行代码。
I want to use an open dialogue box to select it.
我想使用一个打开的对话框来选择它。
This code:
这段代码：
Option Explicit
Private Sub CommandButton1_Click()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer, fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Please select the file."
.Filters.Clear
.Filters.Add "Excel 2003", "*.xls?"
If .Show = True Then
fileName = Dir(.SelectedItems(1))
End If
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open (fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("import-sheets.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("import-sheets.xlsm").Worksheets(total)
Next sheet
Workbooks(fileName).Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
allows me to select the file through an open dialogue box but then the workbook opens in a new window.
允许我通过打开的对话框选择文件，但工作簿会在新窗口中打开。
回答by xarxziux
The syntax looks wrong, but I'm surprised it's not generating an error message. Try assigning the newly opened workbook to a variable and then reference it through that, like this for example:
语法看起来不对，但我很惊讶它没有生成错误消息。尝试将新打开的工作簿分配给一个变量，然后通过它引用它，例如：
Dim ImportBook as Workbook
Dim ExportBook as Workbook
Set ImportBook = Workbooks.Open (fileName)
Set ExportBook = Workbooks.Open ("import-sheets.xlsm")
However it looks like what you're doing is making a copy of the whole workbook and saving it to a new file, in which case there are probably better ways of doing that than one sheet at a time.
但是，看起来您正在做的是制作整个工作簿的副本并将其保存到新文件中，在这种情况下，可能有比一次一张纸更好的方法。
Would the Workbook.SaveAsmethod be more appropriate here?
请问Workbook.SaveAs方法更合适吗？
