Quote character around filename disappearing

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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

Code:
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:

Code:
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

Board Regular
Joined
Jan 18, 2005
Messages
113
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

New Member
Joined
Apr 28, 2002
Messages
18
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.
 

Forum statistics

Threads
1,148,222
Messages
5,745,467
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top