使用 VBA 将外部 Excel 工作表复制到当前工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16565270/
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
Copying external Excel sheet to current workbook using VBA
提问by Syed Meesam Ali
I'm working on small project in which I need to add sheets to the currently open workbook from any external database of worksheets in another workbook. I made a form to import any sheet that is required in the currently open (active) workbook.
我正在处理一个小项目，我需要从另一个工作簿中的任何外部工作表数据库将工作表添加到当前打开的工作簿中。我制作了一个表单来导入当前打开（活动）工作簿中所需的任何工作表。
The sheets will be copied from remote (in any other folder but same computer) workbook. I am using following code but due to unknown reasons the sheets are NOT getting copied to my current workbook.
这些工作表将从远程（在任何其他文件夹中，但在同一台计算机中）工作簿中复制。我正在使用以下代码，但由于未知原因，这些工作表没有被复制到我当前的工作簿中。
Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Dim oWS As String
Set activeWB = Application.ActiveWorkbook
FilePath = "D:\General Required Docs\DATA.xlsm"
If optFirst.Value = True Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
oWS = cboMaterial.Value
Set wb = Application.Workbooks.Open(FilePath)
wb.Worksheets(oWS).Copy
After:=Application.ActiveWorkbook.Sheets(ThisWorkbook.Sheets.count)
activeWB.Activate
wb.Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
回答by Ripster
Change
改变
wb.Worksheets(oWS).Copy
After:=Application.ActiveWorkbook.Sheets(ThisWorkbook.Sheets.count)
to
到
wb.Worksheets(oWS).Copy
After:=activeWB.Sheets(activeWB.Sheets.count)
assuming that oWS is the index of the worksheet you want to copy.
假设 oWS 是您要复制的工作表的索引。
回答by Ramsin Yacoob
Sub Add_Bridge_1()
Dim wbk1 As Workbook, wbk2 As Workbook
'add your own file path
fileStr = "C:\Program Files\Microsoft Office\Office\HL DDS Templates.xlsx"
Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Add(fileStr)
'wbk2.Sheets("Bridge 1").Copy After:=Workbooks("WorkbookNameYouCopyCodeInto").Sheets(1)
wbk2.Sheets("Sheet Name").Copy After:=wbk1.Sheets(1)
wbk2.Saved = True
End Sub
