Open Window Explorer and set selected file to variable

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
Hello all,

Here is a question i have for an issue I'm dealing with. I have a macro that fills in text boxes in a word document. I just found out that for some reason, I have several people who have had issues get the program to open up the file. Several items its just that someone has moved the file to a different location and for a few i found out that they have different drive lettering for the shared drive. My thought solution was that when the macro tries to open the file. If there is an error than there should be a windows explorer window pop open and have the user navigate to the file and then open said file they need. How would i go about doing that and have the file location/name be recorded to a variable. The code below is what I'm currently doing. I just need that code to add in to make this work.

VBA Code:
wfile = "G:\Agreements\Improvement.Agr-2Party.docx"

If Dir(wfile) <> "" Then
    Wordapp.Documents.Open (wfile)
    Wordapp.Visible = True
Else
    MsgBox "Link to file is missing, please use explorer window to select the Improvement.Agr-2Party document "
    'wfile = 'Here is where the window explorer code will go'
    Wordapp.Documents.Open (wfile)
    Wordapp.Visible = True
End If
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
Wildly untested code fragment but should be close

VBA Code:
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Documents", "*.docx", 1
    .AllowMultiSelect = False
    .Show
      
    If .SelectedItems.Count <> 1 Then Exit Sub  ' cancel

    wfile = .SelectedItems(1)

    ' do something useful with wfile

End With
 
Solution

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
Thanks Celly,

I'll try it out as soon as i can and will let you know if it works or if i get an error message.
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
I tried the code and it gave me an error with the following:

msoFileDialogOpen
 

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You are getting a compile error? Is this Windows?

I just created a function in a module and it works fine.

VBA Code:
Sub Rectangle1_Click()

    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Documents", "*.docx", 1
        .AllowMultiSelect = False
        .Show
          
        If .SelectedItems.Count <> 1 Then Exit Sub  ' cancel
    
        MsgBox .SelectedItems(1)
    
    End With

End Sub
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
Hello Celly,

I ended up finding the problem. I didn't have the reference library for office selected so that is why msoFileDialogOpen wasn't recognized.
 

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

So your Microsoft Office XXX Object Library wasn't added as a reference? That's odd, it should be on by default on a new workbook project as far as I know. I don't recall ever having to add it.
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
I'm sorry, the issue was that I'm working on a different project with Late Binding because it's going to be used in an environment that doesn't allow to added reference libraries so i turned off all of the reference libraries except for excel. That way just in case something freaky happened and someone didn't have that library selected. Easier to do late binding instead of dealing with setting hundreds of peoples settings.
 

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
So this is some sort of corporate IT policy? That seems quite draconian. I'm interested in your experience as I have developed a spreadsheet for mass consumption and have never encountered this.

 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
It's not really an IT policy. It's just something that my boss and supervisor requested of me. I'm developing a special tool that automates a few procedures we have to deal with. Its time consuming, so I developed a series of macros to help with doing different functions depending on the information entered into a spreadsheet log. That being said, for whatever reason not everyone's settings are the same and there are a lot of users and it would be time-consuming to figure out who needs their library reference updated/get the right one selected. I did some research and found the concept of Late Binding. It works perfectly with communicating between the different office programs I use which are outlook, excel, and word. Anyways I do understand coding wise it would probably be easier to have all the reference libraries selected and having it work like a normal situation, but for the most part, I feel I'm getting the hang and am close to finishing this beast. Now I just need to work on a few more minor aspects and test it out one machine that seems to sort of representing the ideal environment in that if it doesn't work there, it won't work in most places.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,294
Messages
5,641,392
Members
417,207
Latest member
Vxhaet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top