VBA 打开 Excel 文件并将工作表 1 数据粘贴到当前工作簿中的“RRimport”工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19966526/
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
VBA to Open Excel File and Paste Sheet 1 Data into "RRimport" Sheet in Current Workbook
提问by The Analyst
Ok so I have a current workbook (Original Workbook) with several Sheets. I would like to open an existing workbook (Data Workbook) and copy all of the contents in Sheet 1 of 'Data Workbook', then paste everything into Sheet "RRimport" of 'Original Workbook'. At the end of this process I would like to close the 'Data Workbook' So far I have the following code, however it currently pastes a new sheetright after my sheet names "ARGimport" of my Original Workbook:
好的，所以我有一个包含多个工作表的当前工作簿（原始工作簿）。我想打开一个现有的工作簿（数据工作簿）并复制“数据工作簿”的工作表 1 中的所有内容，然后将所有内容粘贴到“原始工作簿”的工作表“RRimport”中。在此过程结束时，我想关闭“数据工作簿”到目前为止我有以下代码，但是它目前在我的原始工作簿的工作表名称“ARGimport”之后粘贴了一个新工作表：
Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
If Sheet.Visible = True Then
Sheet.Copy After:=wb1.Sheets("ARGimport")
End If
Next Sheet
End If
wb2.Close
End Sub
Thanks to the help of rdhsI was able to figure this out. Updated and working code below:
感谢rdhs的帮助，我能够弄清楚这一点。更新和工作代码如下：
Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range
Set wb1 = ActiveWorkbook
Set PasteStart = [RRimport!A1]
Sheets("RRimport").Select
Cells.Select
Selection.ClearContents
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet
End If
wb2.Close
End Sub
回答by Chel
Does this do what you want?
这是你想要的吗？
Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range
Set wb1 = ActiveWorkbook
Set PasteStart = [RRimport!A1]
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet
End If
wb2.Close
End Sub
