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:\Spreadsheets\MyFile.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