AnsweredAssumed Answered

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

Question asked by Martin Bourgault on Apr 30, 2018
Latest reply on May 1, 2018 by 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

Outcomes