SunlinerDB
10-27-2017, 07:13 AM
Hi,
I'm using Excel 2013 and have found code that gets the user to select the file that needs to be opened for use. The problem I'm having is that it doesn't retain the file path and file name that my other procedures need to use to navigate between the open workbooks unless I call the function or procedure each time the other procedures need the file path and file name. I want to have the user select the file once and then be able to retain the path and name until it's cleared from the variable or the excel is closed. Also, if I click cancel on the folder dialogue I get a Run-time error 424" Object required. So I need some help with error handling. I have pasted my code below. I decided to try the code as procedure vice a function to see if I could get value retention for other procedures to use with no luck. The second procedure I run to see if the value was retrained after running the first one. As I've been fighting and web researching this for three months, assistance is greatly appreciated. Thanks.
Public File_Dialog As FileDialog
Public Source_File_Name As String
Public Source_Workbook As Workbook
Sub GetSourceFile()
' Prompt user to select the source workbook file name from folder dialogue
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DefaultFilePath = "C:\"
Set File_Dialog = Application.FileDialog(msoFileDialogFilePicker)
With File_Dialog
.ButtonName = "Select File"
.Title = "Select a File"
.AllowMultiSelect = False
.Filters.Add "MS Excel Workbook File", "*.xls*", 1
.InitialView = msoFileDialogViewList
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextFileCode1
On Error Resume Next
Selected_File_Item = .SelectedItems(1)
End With
NextFileCode1:
GetFile = Selected_File_Item
Source_File_Name = Selected_File_Item
Set Source_Workbook = Selected_File_Item
Set File_Dialog = Nothing
If Selected_File_Item <> "" Then
MsgBox "The " & Source_File_Name & " workbook folder path and file name has been stored." & Chr(10) & Chr(10) & "The GetFile vairable is: " & GetFile & "." & Chr(10) & Chr(10) & "The Selected_File_Item variable is: " & Selected_File_Item & "."
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
ElseIf Selected_File_Item "" Then
MsgBox "No workbook file was selected."
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
----------------------------------------
Sub SourceFileCheck()
' Check to see if file selection value was retained.
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
End Sub
I'm using Excel 2013 and have found code that gets the user to select the file that needs to be opened for use. The problem I'm having is that it doesn't retain the file path and file name that my other procedures need to use to navigate between the open workbooks unless I call the function or procedure each time the other procedures need the file path and file name. I want to have the user select the file once and then be able to retain the path and name until it's cleared from the variable or the excel is closed. Also, if I click cancel on the folder dialogue I get a Run-time error 424" Object required. So I need some help with error handling. I have pasted my code below. I decided to try the code as procedure vice a function to see if I could get value retention for other procedures to use with no luck. The second procedure I run to see if the value was retrained after running the first one. As I've been fighting and web researching this for three months, assistance is greatly appreciated. Thanks.
Public File_Dialog As FileDialog
Public Source_File_Name As String
Public Source_Workbook As Workbook
Sub GetSourceFile()
' Prompt user to select the source workbook file name from folder dialogue
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DefaultFilePath = "C:\"
Set File_Dialog = Application.FileDialog(msoFileDialogFilePicker)
With File_Dialog
.ButtonName = "Select File"
.Title = "Select a File"
.AllowMultiSelect = False
.Filters.Add "MS Excel Workbook File", "*.xls*", 1
.InitialView = msoFileDialogViewList
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextFileCode1
On Error Resume Next
Selected_File_Item = .SelectedItems(1)
End With
NextFileCode1:
GetFile = Selected_File_Item
Source_File_Name = Selected_File_Item
Set Source_Workbook = Selected_File_Item
Set File_Dialog = Nothing
If Selected_File_Item <> "" Then
MsgBox "The " & Source_File_Name & " workbook folder path and file name has been stored." & Chr(10) & Chr(10) & "The GetFile vairable is: " & GetFile & "." & Chr(10) & Chr(10) & "The Selected_File_Item variable is: " & Selected_File_Item & "."
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
ElseIf Selected_File_Item "" Then
MsgBox "No workbook file was selected."
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
----------------------------------------
Sub SourceFileCheck()
' Check to see if file selection value was retained.
Debug.Print "Selected_File_Item variable = " & Selected_File_Item
Debug.Print "Source_File_Name variable = " & Source_File_Name
Debug.Print "Source_Workbook variable = " & Source_Workbook.Name
End Sub