Opening a Word file from a macro in Excel

I have some code that will open a Word document from a macro in Excel, but for some reason it won't recognize the Word file if the folders in the filepath have any spaces in their names. Here is the code:

Sub MergeToWord()

ActiveWorkbook.Names.Add Name:="MyData", RefersTo:="=Sheet1!" _
& Range(Selection, Selection.End(xlDown)).Address
Shell "C:\Program Files\Microsoft office\Office11\winword.exe F:\\Shared Files\wordfile.doc"

End Sub

The line in red is where the problem is. Since there is a space in "Shared Files" it doesn't recognize the word file. Does anyone know a way around this or why this error occurs

Thanks

Answer this question

Opening a Word file from a macro in Excel

tgs777
Code sample to add a hyperlink:

Range(TargetRange).Hyperlinks.Add _
Anchor:=Range(TargetRange), _
Address:=vaFiler, _
TextToDisplay:=TextToDisplay

To get the details of where the Hyperlink is pointing:

Filepath = Range("Database").Hyperlinks(1).Address

Two methods to follow the hyperlink:

Dim Filepath As String
Filepath = Range("Database").Hyperlinks(1).Address
Dim CloseWorkbook As Boolean

If Not IsWorkbookOpen(Filepath) Then
Workbooks.Open Filename:=Filepath
CloseWorkbook = True
End If

Or:

Range("Database").Hyperlinks(1).Follow
Martyn Lovell

Chr(34) is another way to code ", each character has a numeric value (ASCII character code), the Chr() function takes the code and returns the character. 34 is the ASCII code for the " character.

You need it because you wrap text in double quotes... "example".. however if the text contains a double quote then it confuses VB.

"she said "Hello"" for example confuses VB because it doesn't know if you meant "she said" or not... so you have to use Chr(34) to replace the double quotes in the text.

"she said" & Chr(34) & "Hello" & Chr(34)...

The file not found error just means your word document isn't where you said it was, but i think you know that.
Ganga

Hi,

Its expecting the DOS 8.3 filename for the file. Unfortunately the only way I know to convert the long file name to DOS 8.3 is through the Windows API. You don't need to do this though just you wrap the filename in double quotes.

Shell "C:\Program Files\Microsoft office\Office11\winword.exe " & Chr(34) & "F:\Shared Files\wordfile.doc" & Chr(34)
co?kun
I'm still getting an error, although it's slightly different. Now it's a Run-time error 53, file not found. What exactly does the Chr(34) mean I'm pretty new to Visual Basic so I don't know a whole lot about the language. Thanks so much for your help.
Michael1956
I had a similar issue recently.

> In one of your sheets ("Anysheet") in any range ("anyrange"), type an identifier, ie, "User Manual"
> Insert a hyperlink in that cell and browse to the file and click OK.
> Click the new hyperlink to ensure it works.

Worked for me. Hope it helps.

Sub MergeToWord()

ActiveWorkbook.Names.Add Name:="MyData", RefersTo:="=Sheet1!" _
& Range(Selection, Selection.End(xlDown)).Address
Sheets(Anysheet).Range(anyrange).Hyperlinks(1).Follow

End Sub

chris house

AmandaJO wrote:
Sub MergeToWord()

ActiveWorkbook.Names.Add Name:="MyData", RefersTo:="=Sheet1!" _
& Range(Selection, Selection.End(xlDown)).Address
Shell "C:\Program Files\Microsoft office\Office11\winword.exe F:\\Shared Files\wordfile.doc"

End Sub

Try this in place of your Shell command:

Sub OpenWordDoc()
Dim wdDoc As Object
Set wdDoc = GetObject("F:\\Shared Files\wordfile.doc")
wdDoc.Parent.Visible = True
End Sub

Geokri
Put a link to the file in a cell, then use cell.hyperlink(1).follow
Michael J. O.

Amando,

Since there are blanks in a filename you have toe use double-quotes

Just like this:

Sub MergeToWord()

ActiveWorkbook.Names.Add Name:="MyData", RefersTo:="=Sheet1!" _
& Range(Selection, Selection.End(xlDown)).Address
Shell "C:\Program Files\Microsoft office\Office11\winword.exe ""F:\Shared Files\wordfile.doc"""

End Sub

Kind Regards

FiftyFive
Opening a Word file from a macro in Excel

Answer this question