PDA

View Full Version : VBA Excel 2013 Prompting users to select a file & retain value.



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

p45cal
10-27-2017, 07:56 AM
Your Selected_File_Item and Source_File_Name variables ARE being retained (as long as you don't reset the project between running GetSourceFile and running SourceFileCheck).
Source_Workbook won't work properly because you've defined it as a WorkBook but you''ve not opened it:
Set Source_Workbook = Selected_File_Item will not work
Set Source_Workbook = Workbooks(Selected_File_Item)
would be fine if you'd opened that workbook previously with something along the lines of:
Workbooks(Selected_File_Item).open

SunlinerDB
10-27-2017, 08:11 AM
A the purpose of having the user select the file is that it will need to have its data connections refreshed from a SharePoint site to update the data. My next step was to add code that opened the selected file so the user can update the data before the target file fetches data from it. I'll add the code to open the file and go from there.&nbsp; Thank you.

SunlinerDB
10-27-2017, 10:56 AM
Your Selected_File_Item and Source_File_Name variables ARE being retained (as long as you don't reset the project between running GetSourceFile and running SourceFileCheck).
Source_Workbook won't work properly because you've defined it as a WorkBook but you''ve not opened it:
Set Source_Workbook = Selected_File_Item will not work
Set Source_Workbook = Workbooks(Selected_File_Item)
would be fine if you'd opened that workbook previously with something along the lines of:
Workbooks(Selected_File_Item).open


Now that I've added the code to open the selected file, it appears to have solved the file path name retention issue I was having. But I now need to resolve Run-time error 424" Object required I'm getting when I run the code and cancel the folder dialogue. My guess is that because no file is selected when you cancel the dialogue there is nothing to in the Selected_File_Item variable to set the GetFile to which is generating the error. As I want the user to be able to be able to abort if the didn't want to open the selected file, what would be the code need to handle the error? I think this line of code, If .Show <> -1 Then GoTo NextFileCode1 is may be the cause and maybe the label needs to be move or four line of code need to be moved inside my IF that is checking for a null or empty Selected_File_Item variable?

SunlinerDB
10-27-2017, 11:09 AM
Moving the four line of code inside the IF THEN ELSE that follows address the run time error. Thanks for the help on all of this, much appreciated!!!