Open Excel Files - Open Dialog - GetOpenFilename Method

Open Excel Files - Open Dialog - GetOpenFilename Method


The GetOpenFilename Method is a cousin to the CommonDialog. Some of the CommonDialog's tasks can he done with this method



GetOpenFilename Method (Displays the standard Open dialog box and gets a file name from the user without actually opening any files.)


Sub Open_Excel_File_Thru_VBA()

'----------------------------
' Coded for http://vbadud.blogspot.com
'----------------------------

Dim arTemp() As Variant

Dim lRet

On Error GoTo Err_Clr

'Default method Uses Open Dialog To Show the Files
lRet = Application.GetOpenFilename

'Select Only Excel Files - One File at A Time
lRet = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If lRet = False Then
MsgBox "Select a File Please!!!"
End If

'Select Multiple Files - Get Multiple Files as Input
' An array can be used to get the multiple excel files selected by user
arTemp = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
If UBound(arTemp) = 0 Then
MsgBox "Select a File Please!!!"
End If


Err_Clr:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub



Excel VBA, GetOpenFilename Method, Show Dialog, , Show Open Dialog Box

Browse a Folder / Select a Folder Thru Shell

Browse a Folder / Select a Folder Thru Shell

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Here let us use the above shell functions to open the browse directory dialog

Sub Show_BrowseDirectory_Dialog()

' BrowseForFolder
' SHBrowseForFolder API Function Example

Dim dirInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

' Set Default Root folder = Desktop
dirInfo.pidlRoot = 0&

dirInfo.lpszTitle = "Browse directory!"

' Type of directory
dirInfo.ulFlags = &H1

' Show the Browse Dialog
x = SHBrowseForFolder(dirInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
MsgBox "You have selected :=" & Left(path, pos - 1)
Else
MsgBox "Browse a Directory..."
Show_BrowseDirectory_Dialog
End If

End Sub

SHBrowseForFolder Function displays a dialog box enabling the user to select a Shell folder