Open a workbook using FileDialog and manipulate it

Question

I found this code here on 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

I know this code should select a file in FileDialog. However, once I have chosen the .xls file, how do I manipulate the file? In other words, where is my file object for me to manipulate?

I would like someone to continue this code to make some simple manipulation on the workbook so I could learn how to do those simple things on a workbook that I opened.

Answer 1

There are two approaches for you (I prefer using first one). In both approaches wb variable stores opened workbook. I commented code in details, but if you have some questions - ask:)

First approach:

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

Second approach:

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

Answer 2

Here's an example:

Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range

Set wb = Workbooks.Open(txtfilename) ' the file path you selected in FileDialog
Set ws = wb.Worksheets(1)
Set r = ws.Cells(1, 1)

With r    
    .Value = "Hello world!"
    .Interior.Color = RGB(255,20,20) 'bright red
End With