Quote character around filename disappearing

[haw]

I have the following vba code which formats a LINK statement which is then inserted into an existing REF statement (defined by the bookmark) in a MS Word (2000 SP3) document. The "docname" field contains the surrounding double quotes when the text is actually inserted into the bookmark field. But Word seems to take these quotes away if the actual path/file name does not contain any spaces! It leaves the quotes alone if there ARE spaces in the name. I'd rather the quotes stay there regardless. Any idea why this is and how I can prevent it from happening?

And Yes, I know this is MRExcel, not MRWord ... I'm just hoping that someone here can shed some light on this, since you're the best group of VBA experts around!



Dim docname As String, formatting As String

formatting = " \a \r \* MERGEFORMAT"
docname = Chr(34) & "C:Mydocuments\test.doc" & Chr(34) ' enclose docname in double quotes

Set BMRange = ActiveDocument.Bookmarks("BM1").Range
Selection.GoTo What:=wdGoToBookmark, Name:="BM1"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
Text:= "LINK Excel.Sheet.8 " & docname & " Sheet1!CompanyName" _
& formatting, PreserveFormatting:=False
ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="BM1"
End Sub

 

[XL-Dennis]

Hi,

First of all, Your path and name looks a little bit weird, i e it should read:

C:\Mydocuments\test.xls.

Have You tested to surround the expression with brackets {}, i e

Text:= {LINK Excel.Sheet.8 C:\Mydocuments\test.xls Sheet1!CompanyName \a \r \* MERGEFORMAT }

From a general point of view we can replace single quotations with double quotations like the following example shows:

Option Explicit

Sub test()
Dim stDocName As String

'Your present approach.
stDocName = Chr(34) & "C:\Mydocuments\test.xls" & Chr(34)

'Add this line.
stDocName = Replace(stDocName, String(1, 34), """""")

'And here is the output in the immediate window.
Debug.Print stDocName

End Sub

At present I'm not prepared to set up an example in Word based on Your situation but the above should at least give You a start.

 

[haw]

Yes you are correct ... I left off the "root slash" on the directory path in my sample code.

Regarding replacing the quotes ... I actually first tried using "in-line" quotes, i.e., myvar = """a""" places "a" into myvar (including the quotes), then I switched to the CHR(34) technique 'cause what I thought should work just wasn't. In both cases, viewing the variables via VBE Local window shows that the string in VB does indeed have the quote marks on it. And I'm looking at it immediately before it's placed into the word document. I also look at the string just after the line that inserts it into Word. In both cases it looks right (i.e., has the quotes on it)

So my conclusion is that Word is removing the quotes for some reason. When I experiment with different paths and file names, I find that when I use ones that actually have spaces in them, then word leaves the quotes as I specified them. But if there's no spaces in the path/file name, then Word will usually (though not always) replace them. It's just weird!

And regarding the curly braces ... my link is working ok so my braces must be ok. I'm just worried about the quotes because the information being placed into the link is "user configurable" and I expect some of the path/file names will indeed have spaces in them. So I want to make absolutely sure those quotes are placed in like I want.

Thanks for your help.

 

[robfritts]

Haw:

I'm having a similar problem.

I'm using the GetPivotData method which requires elements to be enclosed in quotes and seperated by commas. The middle section of my argument using the method is based on which column the current cell being evaluated is in.

I've got the result I want in the string variable, and when substituded exactly for the variable name within the GetPivotData method call it works well. But when I place the variable there and expect it to properly substitute in that string it gives me a 1004 error when running the method.

If you find a solution to this problem, please advise. I will do the same.