NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
4 replies Latest Post - 2013-01-08T21:46:38Z by SystemAdmin
Pinned topic VBA and OpenFile dialog box
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
I'd like to write some VBA that opens a file dialog box from within SA. The user will be able choose an associated excel file to open from that point. Any idea on which objects I would use for that?
Updated on 2013-01-08T21:46:38Z at 2013-01-08T21:46:38Z by SystemAdmin
Re: VBA and OpenFile dialog box2013-01-08T19:54:12Z in response to SystemAdminI use the MS Office Object Library and the following user-function to allow selection of files.
Public Function RetrieveFiles(ByRef selectedFiles As Scripting.Dictionary, _
ByVal initFolder As String) As Boolean
'selectedFiles is a scripting.dictionary used to hold the selected files
'initFolder is the default folder for the file dialog
Dim fd As FileDialog
Dim fileSelected As Variant
Dim fileList As New Scripting.Dictionary
Dim fileChosen As Integer
Set fd = myWord.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True 'set this to false if you want to limit selection to only one file
.InitialView = msoFileDialogViewList
.InitialFileName = initFolder
fileChosen = fd.Show
If fileChosen 0 Then
RetrieveFiles = True
For Each fileSelected In fd.SelectedItems
selectedFiles.Add fileSelected, fileSelected
RetrieveFiles = False
Re: VBA and OpenFile dialog box2013-01-08T20:36:01Z in response to SystemAdminThanks Phil - I was thinking along those lines, but with the 'fd as FileDialog' statement, it is saying "User-Defined Type not defined". Somehow it is not able to reference the MS Office library? The intellesense doesnt list it as a option either.
Re: VBA and OpenFile dialog box2013-01-08T21:23:34Z in response to SystemAdminMake sure you include the reference to the office object library (shown in the attached file). Your version might be different.
Since you know you just want to select/open an excel file, you could just reference the Excel object library - I think the filedialog object is also included there.
Re: VBA and OpenFile dialog box2013-01-08T21:46:38Z in response to SystemAdminWow - thank you Phil. I am still new to this and didn't know I could make those references through that dialog. There were a ton of libraries listed. It looks like I have Microsoft 14 libraries, not sure how that will impact what we're doing. I'll give it a try tomorrow and see what happens. Thanks!