How to call Open Common Dialog in Excel VBA

[vb6novice]

Please tell me how to call the Open Common Dialog box in VBA code for Excel 97.

All help is greatly appreciated.

 

[DrBowes]

Application.Dialogs(xlDialogOpen).Show

 

[vb6novice]

Thanks Doc. It works great. Now, how do I get the result of the the file selected when the user clicks Open?

 

[Loomah]

Hi vb6
It might depend on what you want to do. Using the method Dr has suggested will open the file when you click open. You could then get the name using

ActiveWorkbook.Name

in the next line of code.

Another method you could use is 'GetOpenFilename'
eg

Sub a()
Dim sFile As String
sFile = Application.GetOpenFilename _
    ("Excel files (*.xls), *.xls", , "Select a File")
MsgBox sFile
End Sub

This doesn't open the file but returns the full path and file name. If you then want to open the file you need to use the open method. There is an example in this thread: thread707-603390

To get just the file name takes a little string manipulation
eg

MsgBox Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

;-)

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