MS EXCEL VBA - 我需要将工作表从一个excel文件导入到另一个excel文件
问题描述:
我需要从一个excel工作簿(工作表名称不总是相同)导入工作表并将其导入当前活动工作簿。MS EXCEL VBA - 我需要将工作表从一个excel文件导入到另一个excel文件
这是我到目前为止有:
Sub openFile_Click()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
wb2 = Workbooks(FileToOpen) 'This is where I am stuck..I can't give it a static name
For Each Sheet In wb1.Sheets
If Sheets.Visible = True Then
Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
End If
Next Sheet
End If
答
将工作簿上打开(或设置工作簿后没有文件路径)
在这里你去:
Sub openFile_Click()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open(FileToOpen)
For Each Sheet In wb1.Sheets
If Sheet.Visible = True Then
Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
End If
Next Sheet
End If
End Sub
答
这段代码可以用于你想要的东西。我做了以下更正。
将变量的所有声明移到开始的过程中,以便在使用它们之前声明它们。这只是一个好习惯。
在打开第二个工作簿之前,将您的活动工作簿分配给该变量,以便只打开一个工作簿。
-
您对每个声明也有一些更正。
Sub openFile_Click() Dim wb1 As Workbook Dim wb2 As Workbook Set wb1 = ActiveWorkbook FileToOpen = Application.GetOpenFilename _ (Title:="Please choose a Report to Parse", _ FileFilter:="Report Files *.rpt (*.rpt),") 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(wb1.Sheets.Count) End If Next Sheet End If End Sub