Public Function SelectFile() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 3-Aug-2009
'
'On Error GoTo ErrorHandler
Dim fd As Office.FileDialog
Dim varSelectedItem As Variant
Dim strFileNameAndPath As String
Dim strFolderPath As String
Dim fds As Office.FileDialog
Dim strPath As String
Dim SelectFolder As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Set fds = Application.FileDialog(msoFileDialogFolderPicker)
strPath = "c:\"
With fds
.Title = "Browse for folder where _________ are located"
.ButtonName = "Select"
.InitialView = msoFileDialogViewDetails
'.InitialFileName = strPath
If .Show = -1 Then
strFolderPath = CStr(fd.SelectedItems.Item(1)) & "\"
Else
Debug.Print "User pressed Cancel"
strFolderPath = ""
End If
End With
SelectFolder = strFolderPath
MsgBox SelectFolder
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
'Set AllowMultiSelect to True to allow selection of multiple files
.AllowMultiSelect = True
.Title = "Browse for File"
.ButtonName = "Select"
.Filters.Clear
.Filters.Add "Documents", "*.*", 1
' .Filters.Add "Documents", "*.doc; *.txt", 1
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
'Get selected item in the FileDialogSelectedItems collection
For Each varSelectedItem In .SelectedItems
strFileNameAndPath = CStr(varSelectedItem)
Dim FileNameWithExt
FileNameWithExt = Mid$(strFileNameAndPath, InStrRev(strFileNameAndPath, "\") + 1)
' MsgBox FileNameWithExt
' MsgBox varSelectedItem
' MsgBox strFileNameAndPath
' MsgBox SelectFolder
FileCopy varSelectedItem, SelectFolder & FileNameWithExt
'
Next varSelectedItem
Else
Debug.Print "User pressed Cancel"
strFileNameAndPath = ""
End If
End With
SelectFile = strFileNameAndPath
'ErrorHandlerExit:
' Set fd = Nothing
' Exit Function
'
'ErrorHandler:
' MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
' Resume ErrorHandlerExit
End Function
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE