Open a workbook using FileDialog and manipulate it in Excel VBA

Question

I am learning how to use Excel macros and I found this code:

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

This code opens the FileDialog. How can I open the selected Excel file without over-writing the previously opened?

What do you mean by "without over-writing the previously opened"? This code only saves the path of chosen file. Anyway, if you open a file using `CTRL + O` you do not over-write a file. Please clarify your question. — lowak, Aug 06 '14 at 06:57
yes this code just save the path,but i want to open the selected file.and in case i again run the macro,it should open the excel file in a new workbook. — user3576866, Aug 06 '14 at 07:05

score 27 · Accepted Answer · edited Feb 27 '17 at 21:59

Thankyou Frank.i got the idea. Here is the working code.

Option Explicit
Private Sub CommandButton1_Click()

  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
  Dim fd As Office.FileDialog

  Set fd = Application.FileDialog(msoFileDialogFilePicker)

  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls?"

    If .Show = True Then
      fileName = Dir(.SelectedItems(1))

    End If
  End With

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Workbooks.Open (fileName)

  For Each sheet In Workbooks(fileName).Worksheets
    total = Workbooks("import-sheets.xlsm").Worksheets.Count
    Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("import-sheets.xlsm").Worksheets(total)
  Next sheet

  Workbooks(fileName).Close

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

End Sub

score 8 · Answer 2 · answered Aug 06 '14 at 07:02

8

Unless I misunderstand your question, you can just open a file read only. Here is a simply example, without any checks.

To get the file path from the user use this function:

Private Function get_user_specified_filepath() As String
    'or use the other code example here.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Please select the file."
    get_user_specified_filepath = fd.SelectedItems(1)
End Function

Then just open the file read only and assign it to a variable:

dim wb as workbook
set wb = Workbooks.Open(get_user_specified_filepath(), ReadOnly:=True)

answered Aug 06 '14 at 07:02

Frank

444
2
10

7

It is missing the instruction to show the FileDialog: fd.Show – Alex Feb 20 '18 at 21:44
Don't miss to destroy the "fd" variable using: Set fd = Nothing into the last line of function, and "wb" too. – Marcus Poli May 06 '21 at 17:41
A bit late for a response, but I'll add it for completeness. It's not required to set the fd variable to nothing. It goes out of scope when the function call is completed and then it gets garbage collected. – Frank Nov 24 '22 at 12:20

Open a workbook using FileDialog and manipulate it in Excel VBA

2 Answers2

Linked