Open Word Docs with GetOpenFilename 1

[Pudsters]

Below is my code to select and open Excel files. Can someone modify it to include showing Word documents and be able to open them as well? I added this: "Word Documents (*.doc),*.doc," which showed the Word files, but I got an error message when I tried to open the Word documents.


Sub ()
Dim fn As Variant
ChDir "C:\Documents"
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Technician Technical Information - Select folder and file to open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn
End Sub

Thanks!

 

[KenPuls]

Are you looking for a version to use from Word, or one to use from Excel to open the file in Word?

I can do either, but the former is less code than the latter.

Ken Puls, CMA

http://www.excelguru.ca

 

[KenPuls]

Here,

Assuming you want to do it from Excel, I've adapted the method shown at my site here:

http://www.excelguru.ca/node/10

Note that this makes use of late binding, so no references are required.

Sub OpenFiles()
Dim fn As Variant

ChDir "C:\Documents"
    fn = Application.GetOpenFilename("Excel-files,*.xls,Word Files, *.doc", _
        1, "Technician Technical Information - Select folder and file to open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    ' the user didn't select a file
    Debug.Print "Selected file: " & fn
    Select Case Right(fn, 3)
    Case Is = "xls"
        Workbooks.Open fn
    Case Is = "doc"
        Call GetWord(CStr(fn))
    Case Else
        MsgBox "How did you select a non xls or doc file?"
    End Select
End Sub

Sub GetWord(strFilePath As String)
'Bind to an existing or created instance of Microsoft Word
Dim objApp As Object

'Attempt to bind to an open instance
On Error Resume Next
Set objApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
    'Could not get instance, so create a new one
    Err.Clear
    On Error GoTo ErrHandler
    Set objApp = CreateObject("Word.Application")
    With objApp
        .Visible = True
    End With
Else
    'Bound to instance, activate error handling
    On Error GoTo ErrHandler
End If
        
'Open the file
objApp.Documents.Open strFilePath

ErrHandler:
'Release the object and resume normal error handling
Set objApp = Nothing
On Error GoTo 0
End Sub

Ken Puls, CMA

http://www.excelguru.ca

 

[Pudsters]

Thanks Ken, works perfect! Seems like a lot of extra code though just to add the ability to open a Word Doc!

Puds!

 

[KenPuls]

LOL!

Actually, all the extra code is to bind to/create and instance of Word. If you were trying to open an excel template instead of an xls file, you wouldn't need the second routine. The changes for that would have been the exact same length as the modifications that I made to your first routine.

You could also shorten the second routine if you could 100% guarantee that a user would always have an instance of Word open already. A large portion of that second routine is to check, and create a word instance if one isn't already open. I wouldn't ever bet that my user would make my life easy, though, so better safe than sorry.

Cheers, and thanks for the Star!

Ken Puls, CMA

http://www.excelguru.ca