Opening File Open Dialog Box From VB 1

[Kevsim]

I wish to run an Excel macro, where I need to select different directories and file names to open.
I am unsure how to have the File Open dialog box open in VB.
Then the person selects the directory and file for opening.
I would appreciate some assistance.
kevsim

 

[Loomah]

Hi
This should get you on your way. Note that GetOpenFilename returns a string so you need to open the selected book through code.

Sub OpenFile()
Dim sFile As String
Dim wbOpened As Workbook
sFile = Application.GetOpenFilename
If sFile <> "" Then
    Set wbOpened = Workbooks.Open(sFile)
    'do stuff to wbOpened
    'wbOpened.Close
    'Set wbOpened = Nothing
End If
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Molby]

In VBA the code to open the dialog box is

Application.Dialogs(xlDialogOpen).Show

 

[Loomah]

Hi again
There's a slight error in the code I posted

If sFile <> "" Then

should be

If sFile <> "False" Then

That said, the code Molby posted removes the need for this test! I need to wake up!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[VBAjedi]

Loomah,
Using .GetOpenFileName does have a potential advantage in that it allows the developer control over what is done with the file the user selects (whereas Dialogs(xlDialogOpen).Show just automatically opens it).

Kevsim,
If that functionality would be useful to you, here's a code frag I use to make sure the user specifies a file:

Dim NewFileName As String
Do
   NewFileName = Application.GetOpenFilename _
   ("Microsoft Excel Workbook (*.xls),*.xls")
   If NewFileName = "False" Then
      x = MsgBox("No file specified. Try again?", vbOKCancel)
      If x = 2 Then Exit Sub
   End If
Loop While NewFileName = "False"
Workbooks.Open (NewFileName)

Worth noting is that the same code can be used with .GetSaveAsFilename to show that dialog. . .

VBAjedi

 

[Loomah]

Jedi
You are right, of course. I knew there was a reason why I did it that way and assigned the opened book to a variable!!

I still haven't done that waking up thing yet which may have had some bearing on the interview I had yesterday!

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Kevsim]

I thank you all for the info.
I now have the code working OK.
kevsim

 

[CuriousEric]

Is there any way to do the same with directories? Meaning, is there a way to open a dialog that just shows the directories that could be selected?

 

[PHV]

open a dialog that just shows the directories that could be selected
You may create a function like this:

Function PickFolder(strStartDir As Variant) As String
    Dim SA As Object, f As Object
    Set SA = CreateObject("Shell.Application")
    Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not f Is Nothing) Then
        PickFolder = f.Items.Item.path
    End If
    Set f = Nothing: Set SA = Nothing
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CuriousEric]

Worked like a charm -- thanks!