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.
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
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
