Below function displays a file picker dialog and returns the picked file path as String.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Displaying a Windows explorer standard file picker dialog to user and returning picked file path as String.
' @Returns - A String holding a full file path. If user aborted file picker dialog the returned String is empty("").
Public Function getSingleFilePath() As String
    Dim myFileDialog As FileDialog
    Dim filePath As String
    
    ' Search for file.
    Set myFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    myFileDialog.InitialView = msoFileDialogViewDetails
    
    ' Prompt dialog to user.
    If myFileDialog.Show = -1 Then
        filePath = myFileDialog.SelectedItems.Item(1)
    End If
    
    getSingleFilePath = filePath
End Function

Last Updated: 09 October 2014

Hits: 4042

In case you need the temp directory on a Windows machine in VBA, the following method might do the trick for you.

Source Code

It looks up the TMP or TEMP system environment variable.

' @Author - Alexander Bolte
' @ChangeDate - 2017-05-09
' @Description - Gets the path to a directory either available
' under the "TMP" or "TEMP" system environment variable.
' @Returns a String providing the directory registered in the users
' system under the corresponding environment variable.
Public Function getTempDirectory() As String
    Dim dirPath As String
    
    dirPath = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))
    
    getTempDirectory = dirPath
End Function

Last Updated: 12 May 2017

Hits: 3822

In one of my projects I had to read all worksheet names from several Excel files.

Here is the code for getting all worksheet names from a closed or already opened Excel file.

' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in workbook available under given file path.
' @Param filePath - Path to Microsoft Excel file the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel file.
Public Function getSheetNames(ByVal filePath As String) As VBA.Collection
    Dim wrk As Workbook
    Dim sheetNames As New VBA.Collection
    
    On Error GoTo errHandle:
    
    Set wrk = getOpenedWorkbook(filePath)
    If wrk Is Nothing Then
        Set wrk = Application.Workbooks.Open(filePath)
    End If
    ' Get the sheet names for one file.
    Set sheetNames = getSheetNamesFromWorkbook(wrk)
    ' Close workbook without saving.
    wrk.Close False
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    Set wrk = Nothing
    
    Set getSheetNames = sheetNames
End Function

Referenced Functions can be found below. 

getSheetNamesFromWorkbook(wrk)
Getting all sheet names from a workbook

getOpenedWorkbook(filePath)
Getting an opened Excel workbook

Last Updated: 25 July 2015

Hits: 3925