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