Open Window Explorer and set selected file to variable

[zoog25]

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.

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

 

[Celly]

Wildly untested code fragment but should be close

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

 

[zoog25]

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]

I tried the code and it gave me an error with the following:

msoFileDialogOpen

 

[Celly]

You are getting a compile error? Is this Windows?

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

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]

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]

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]

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]

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.

Retireator.org – Retireate Away!

 

[zoog25]

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.