CODEWITHSUNDEEP
vbaexcel
TJLD22
TJLD22

Reputation: 145

Excel macro: Browse Excel file and use its worksheet data

I write a script like this:

Sub Button_Click()
    objFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ") ' choose load path
    .....

    Call main_function
End Sub

This is the script of an Excel macro button to let the user browse the file. Actually, I want use this to load an Excel file and use that Excel file's data in the main_function(the current excel).

How can I do this?

Upvotes: 3

Views: 16265

Answers (2)

AshishPandey
AshishPandey

Reputation: 31

In continuation to @Larry's answer, you may use the following module if needs to refer the sheet by CodeName, or else if want to refer by sheet name you can use worksheets option, thanks to MVP-Juan Pablo González for this answer over another blog:

'Strating sub procedure to write VBA Code to Open an only Excel 2007 macro Files using File Dialog Box
Sub Browse_File()
Dim strFileToOpen As String
Dim Wbk_WeeklyTemplate As Workbook
Dim Tgt_ShtNm As String
Dim Src_ShtNm As String

'Choosing an Excel File using File dialog Box and capturing the file path in the variable
strFileToOpen = Application.GetOpenFilename(Title:="Please select the file to open", FileFilter:="Excel Files *.xlsm (*.xlsm),")

'Checking if file is selected
If strFileToOpen = "False" Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
Else
    Set Wbk_WeeklyTemplate = Workbooks.Open(strFileToOpen)
End If

''Refer sheet by Sheet CodeName
Src_ShtNm = SheetName(Wbk_WeeklyTemplate, "sheetCodeName")
Tgt_ShtNm = SheetName(ThisWorkbook, "sheetCodeName")

Wbk_WeeklyTemplate.Sheets(Src_ShtNm).Range("N15:X18").Copy
ThisWorkbook.Sheets(Tgt_ShtNm).Range("A1").PasteSpecial xlPasteValues

End Sub


''The function SheetName returns the actual name of the sheet by passing sheet code name
Function SheetName(Wb As Workbook, CodeName As String) As String
    SheetName = Wb.VBProject.VBComponents(CodeName).Properties("Name").Value
End Function

The link to as MVP-Juan Pablo González solution: https://www.mrexcel.com/forum/excel-questions/58845-codename-selecting-sheets-through-visual-basic-applications.html

Hope that helps.

Upvotes: 3

Larry
Larry

Reputation: 2794

Guess you want to restrict the user to Excel only, so I modified the filter for you

Dim pathString As String
Dim resultWorkbook As Workbook
Dim found As Boolean
pathString = Application.GetOpenFilename(fileFilter:="All Files (* . xl*) , *.xl* ")

' check if it's already opened
For Each wb In Workbooks
    If InStr(pathString, wb.Name) > 0 Then
        Set resultWorkbook = wb
        found = True
        Exit For
    End If
Next wb

If Not found Then
    Set resultWorkbook = Workbooks.Open(pathString)
End If

' then you can use resultWorkbook as a reference to the Excel Workbook Object

Upvotes: 4

Related Questions