VBA 打开任何工作簿

Question

我有一个宏，可以从特定文件夹打开电子表格，并将输出保存到另一个工作簿中名为 Sheet1 的工作表中。如果文件名称为"MyFile.xls"，则宏有效，但我希望它能够在任何文件名上运行，但它必须具有"Book2"工作表。

这是我的代码：

Dim source As Workbook
Dim output As Workbook
Dim sourceSheet as WorkSheet
Dim outputSheet as WorkSheet
Dim file As String
file = "C:SpreadsheetsMyFile.xls"  'I would like it to handle any files from any location'
Set output = ThisWorkBook
output.Activate
If Len(Dir$(file)) > 0 Then
    Set source = workbooks.Open(file)
Set sourceSheet = source.Worksheets("Book2") 'Must only run if the sheet is called Book2'
Set outputSheet = output.Worksheets("Sheet1") 'Saves sheets into a new sheet called Sheet1'
End Sub

Answer 1

这是你正在尝试的吗？（久经考验）

Sub Sample()
    Dim source As Workbook, output As Workbook
    Dim sourceSheet As Worksheet, outputSheet As Worksheet
    Dim File
    '~~> Show a dialog to open any excel file
    File = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If File = False Then Exit Sub
    Set output = ThisWorkbook
    If Len(Dir$(File)) > 0 Then
        Set source = Workbooks.Open(File)
        '~~> Error check to see if the workbook has that sheet
        On Error Resume Next
        Set sourceSheet = source.Worksheets("Book2")
        If Err.Number = 0 Then
            Set outputSheet = output.Worksheets("Sheet1")
            '
            '~~> Rest of your code
            '
        Else
            MsgBox "Not found"
            source.Close SaveChanges:=False
        End If
        On Error GoTo 0
    End If
End Sub