file - 使用 FileDialog 打开工作簿并对其进行操作

Question

我在 StackOverflow 上找到了这段代码:

Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd

        .AllowMultiSelect = False
        .Title = "Please select the file to kill his non colored cells"
        .Filters.Add "Excel", "*.xls"
        .Filters.Add "All", "*.*"

        If .Show = True Then
            txtFileName = .SelectedItems(1)
        End If

    End With

我知道这段代码应该在 FileDialog 中选择一个文件。但是，一旦我选择了 .xls 文件，我该如何操作该文件？换句话说，我要操作的文件对象在哪里？

我希望有人继续这段代码来对工作簿进行一些简单的操作，这样我就可以学习如何在我打开的工作簿上做这些简单的事情。

Answer 1

有两种方法适合您(我更喜欢使用第一种)。在这两种方法中，wb 变量存储打开的工作簿。我详细评论了代码，但如果您有任何问题 - 问:)

第一种方法:

Sub test1()
    Dim xlFileName
    Dim wb As Workbook

    xlFileName = GetOpenFilename("Excel (*.xls*),*.xls*", 1, _
        "Please select the file to kill his non colored cells")

    'if user pressed CANCEL - exit sub
    If xlFileName = False Then
        MsgBox "User pressed CANCEL"
        Exit Sub
    End If

    'Tries to open workbook with choosen file name
    On Error Resume Next
    Set wb = Application.Workbooks.Open(xlFileName)
    On Error GoTo 0

    'If we can't find workbook with choosen path, exit Sub
    If wb Is Nothing Then
        MsgBox "Can't find file"
        Exit Sub
    End If

     'your code here
    wb.Worksheets("Sheet1").Range("A1").Value = "test"

    'close workbook with saving changes
    wb.Close SaveChanges:=True
    Set wb = Nothing

End Sub

第二种方法:

Sub test()
    Dim xlFileName As String
    Dim fd As Office.FileDialog
    Dim wb As Workbook

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd

        .AllowMultiSelect = False
        .Title = "Please select the file to kill his non colored cells"
        .Filters.Add "Excel", "*.xls*"
        .Filters.Add "All", "*.*"

        If .Show Then
           xlFileName = .SelectedItems(1)
        Else
           'if user pressed CANCEL - exit sub
           MsgBox "User pressed CANCEL"
           Exit Sub
        End If

    End With

    'Tries to open workbook with choosen file name       
    On Error Resume Next
    Set wb = Workbooks.Open(xlFileName)
    On Error GoTo 0

    'If we can't find workbook with choosen path, exit Sub
    If wb Is Nothing Then
        MsgBox "Can't find file"
        Exit Sub
    End If

    'your code here
    wb.Worksheets("Sheet1").Range("A1").Value = "test"

    'close workbook with saving changes
    wb.Close SaveChanges:=True
    Set wb = Nothing

End Sub