How to open a 'specific' file via 'browse'

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
Hello,

I have doubt that I had been trying to find a solution for a long time. I want to open a specific folder while the browse option is in action to prompt user to select a file to open.
I have tried the following code but the problem is its open up desktop or the last opened folder. I always want to prompt folder ABC (Location: Desktop/ABC) as a first choice for user to select file from?
Code:
Sub test()
MsgBox "Choose Your Source File to Open"
Source = Application.GetOpenFilename()
If Source = "False" Then Exit Sub
Workbooks.Open Source
End Sub

I dont know if the above statement explains my question or not. Please let me know if you have any doubt or queries.
Thank you in advance
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Try using a few lines like this:

Code:
    ChDrive ("M") [COLOR=#008000]'Sets  the current drive[/COLOR]
    ChDir ("M:\Operations\Your File Directory\")[COLOR=#008000] 'Sets the current file directory[/COLOR]
    Source= Application.GetOpenFilename(FileFilter:="Excel Files ( .XLS), *.XLSX", Title:="Please pick a source reference file")
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi

You can navigate to the drive and then to the folder and use the old GetOpenFilename() method of the Application, ...
but you can also use the more recent FileDialog property that allows you to specify the initial folder.

This is an example:

Code:
Sub GetFile()
    Dim sPathname As String
    
    Const sInitialFoldr As String = "c:\tmp\" ' Initial folder
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Please select a file"
        .InitialFileName = sInitialFoldr
        .AllowMultiSelect = False

        If .Show = False Then
            Exit Sub
        Else
            sPathname = .SelectedItems(1)
        End If
    End With
    
    MsgBox sPathname
End Sub
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
pgc01,

Thanks for the alternative method! I am always happy to see different methods. I learn something new everyday.... most of the time more then one thing :)
 

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
A-W-E-S-O-M-E!! Thanks alot guys!
Being a newbie I have learned so much that a macro can do. I kinda really like excel-VBA
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Glad to help. Just hang around the forum and you'll learn loads more :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,445
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top