2 Replies Latest reply on May 1, 2018 7:12 AM by Martin Bourgault

    SW VBA code to get file name with path and extension from browse open window

    Martin Bourgault

      Hi, I have a small project that will used API code.  I have received support from my VAR but is code is somewhat slow and does work on my laptop but not at my work (PDM or Word related, I can't tell yet.)

       

      The objective is fairly simple:

      1- Click on a button in an open form to open a Browsing window to select a file (filter to be set to either .xls or .slddrw)  Not filter is also fine.

      2- Return the full path of the selected file and send it to a string variable. I don't want to open this file yet.  This file is not the active part or drawing.

       

      Sound simple yes, but it is not working yet.  Here is the VAR code, which is using word ??  I am providing this as a start but would rather start from scratch if someone has a better way to proceed.  This is the last missing piece for my "whole" project.  A little help would greatly be appreciated !

       

      Private Sub Browse_List_Click()

       

      Dim fso As Object

      Dim objWord As Object

      Dim WshShell As Object

      Dim strInitialPath As String

      Dim File As Variant

      Dim objFile As Object

      Dim WScript As Object

       

      'set the type of dialog box you want to use

      '1 = Open

      '2 = SaveAs

      '3 = File Picker

      '4 = Folder Picker

      Const msoFileDialogOpen = 1

       

      Set fso = CreateObject("Scripting.FileSystemObject")

      Set objWord = CreateObject("Word.Application")

      Set WshShell = CreateObject("WScript.Shell")

       

      'where you want to start looking for files

      'You could use a string like "C:\Somefolder\Somefolder\"

      'I chose to use the desktop folder of whoever was running the script.  On Windows 7 it's "C:\Users\Username\Desktop\"

      'Run "set" from a command prompt to see the available environment variables

      'strInitialPath = WshShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\"

       

      'Set by default to C: drive

      strInitialPath = "c:\"

      'set the dialog box to open at the desired folder

      objWord.ChangeFileOpenDirectory (strInitialPath)

       

      With objWord.FileDialog(msoFileDialogOpen)

         'set the window title to whatever you want

         .Title = "Select the file to process"

         'I changed this to false because I'm working with one file at a time

         .AllowMultiSelect = False

         'Get rid of any existing filters

         .Filters.Clear

         'Show only the desired file types

         'for each desired group of file types, add a "Filters.Add" line with a different description and desired extensions

         'the dialog box will open using whichever filter is first

         'you can switch to a different filter from a drop-down list on the dialog box

         .Filters.Add "Excel Files", "*.xls;*.xlsx"

         .Filters.Add "All Files", "*.*"

         '.Filters.Add "Text Files", "*.txt"

         '.Filters.Add "Various Files", "*.xls;*.doc;*.vbs"

              

         '-1 = Open the file

         ' 0 = Cancel the dialog box

         '-2 = Close the dialog box

         'If objWord.FileDialog(msoFileDialogOpen).Show = -1 Then  'long form

         If .Show = -1 Then  'short form

            'Set how you want the dialog window to appear

            'it doesn't appear to do anything so it's commented out for now

            '0 = Normal

            '1 = Maximize

            '2 = Minimize

            'objWord.WindowState = 2

       

       

            'the Word dialog must be a collection object

            'even though I'm using one file, I had to use a For/Next loop

            '"File" returns a string containing the full path of the selected file

          

            For Each File In objWord.FileDialog(msoFileDialogOpen).SelectedItems  'long form

               Set objFile = fso.GetFile(File)

                  'Get string from object

                  List.Text = objFile

                  'Store String to Public variable

                  List_txt = List.Text

            Next

         Else

         End If

      End With

       

       

      'Close Word

      objWord.Quit

          

      End Sub

       

      Thanks