Performing simple macro

[ai1094]

Hi,

I have a button with an assigned macro to it. I want the user to be able to click the button and have an option to choose an excel file from your desktop/folder/etc. and create a new sheet to insert the excel file. Then have the macro perform it's function on the excel file that the user clicked. How would I go about this? Thanks.

 

[Yongle]

Try this

Sub Button1_Click()
    Dim fNameAndPath As Variant, wb As Workbook, ws As Worksheet
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS*), *.XLS*", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Set wb = Workbooks.Open(fNameAndPath)
    Set ws = wb.Sheets.Add(before:=wb.Sheets(1))
    
    'do other stuff...
End Sub

Within the same procedure you can refer to the new sheet with ws and the opened workbook with wb