Select File from Directory Query not working

[gruntingmonkey]

I have some code which is opening up a file directory from Excel but I have a couple of issues.
Primarily it debugs when the file is being set (Bolded below). I get a type mismatch and I cant figure it out why!

Further to that, and this isnt important really, but is there a way of limiting results to word files, ie excel, PDFs etc dont show in the directory to be choosen?

Sub SelectProposalFromDirectory()

Dim SummaryWB As Documents
Dim vrtSelectedItem As Variant
Dim UserNm As String

UserNm = (Environ$("Username"))

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "C:\Users\" & UserNm & "\Workplace Name\Sales - Documents\Proposals\"
    .AllowMultiSelect = False
    .Show
    For Each vrtSelectedItem In .SelectedItems
       [B] Set SummaryWB = Documents.Open(vrtSelectedItem)[/B]
    Next
End With

If SummaryWB Is Nothing Then Exit Sub


End Sub

Any help would be appreciated!

 

[Norie]

SummaryWB should not be declared as Documents.

I think it should be declared as Document but I'm not sure as it's not clear where you are running this code or what type of files you are working with.

 

[gruntingmonkey]

When changed to Document it brings up a "Object required" error.

The code is literally just a button in Excel to bring up a folder location on Sharepoint. My intentions is to open a word file.

SummaryWB can be changed if required.

 

[Norie]

If this code is in Excel then Document/Documents don't really exist.

If you want to open a Word document with code in Excel you need to an instance of Word.

Try something like this.

Option Explicit

Sub SelectProposalFromDirectory()
Dim wrdApp As Object
Dim SummaryWB As Object
Dim UserNm As String

    UserNm = Environ$("Username")

    With Application.FileDialog(msoFileDialogOpen)
    
        .InitialFileName = "C:\Users\" & UserNm & "\Workplace Name\Sales - Documents\Proposals\*.doc*"
        
        .AllowMultiSelect = False
        
        If .Show = -1 Then
            Set wrdApp = CreateObject("Word.Application")
            Set SummaryWB = wrdApp.Documents.Open(.SelectedItems(1))
            wrdApp.Visible = True
        Else
            MsgBox "Cancelled!", vbInformation
        End If
        
    End With

End Sub

 

[gruntingmonkey]

Oh, that makes sense now from something I learned earlier today.

Thank you so much for your help.

 

[gruntingmonkey]

Actually, Is there a simple way of also getting the filepath and name at at the same time?

 

[Norie]

Not sure what you mean.

The way you are getting the path in the code seems pretty straightforward.

 

[gruntingmonkey]

....I'm being slow today.

Thats placing the location into the file dialog. I want it once a file has been chosen. Something like.

filepath="C:\Users" & UserNm & "\Blah Blah\Sales - Documents\Proposals\Cheese\Sandwich"
filename=(.SelectedItems(1)) 'yum.doc

I think thats what I want!

 

[Norie]

Try this.

Option Explicit

Sub SelectProposalFromDirectory()
Dim wrdApp As Object
Dim SummaryWB As Object
Dim UserNm As String
Dim strFileName As String
Dim strFullPath As String
Dim strPath As String

    UserNm = Environ$("Username")

    With Application.FileDialog(msoFileDialogOpen)
    
        .InitialFileName = "C:\Users\" & UserNm & "\Workplace Name\Sales - Documents\Proposals\*.doc*"
        
        .AllowMultiSelect = False
        
        If .Show = -1 Then
        
            strFullPath = .SelectedItems(1)
            
            strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
            strPath = Replace(strFullPath, strFileName, "")
            
            Set wrdApp = CreateObject("Word.Application")
            Set SummaryWB = wrdApp.Documents.Open(strFullPath)
            wrdApp.Visible = True
        Else
            MsgBox "Cancelled!", vbInformation
        End If
        
    End With

End Sub

 

[gruntingmonkey]

Thanks Norie. I really appreciate the update on this. I can definitely work with this now to get to my goal!