Driving me insane " marks and Chr(34)

[nick262b]

Hi guys,

So this is driving me insane I have a macro which is exporting to a text file and I need the first line to be:

<ProjectInput xmlns="

http://tempuri.org/ProjectInput.xsd">

I have tried the following codes with the following responses:

As expected

Cells(1, 1).Value = "<ProjectInput xmlns=" & "

http://tempuri.org/ProjectInput.xsd"

& ">"

gives

<ProjectInput xmlns=

http://tempuri.org/ProjectInput.xsd>

but when I try to add the ""

Cells(1, 1).Value = "<ProjectInput xmlns=" & Chr(34) & "

http://tempuri.org/ProjectInput.xsd"

& Chr(34) & ">"

gives

"<ProjectInput xmlns=""

http://tempuri.org/ProjectInput.xsd"">"

Cells(1, 1).Value = "<ProjectInput xmlns=" & """

http://tempuri.org/ProjectInput.xsd"""

& ">"

gives

"<ProjectInput xmlns=""

http://tempuri.org/ProjectInput.xsd"">"

What's going wrong? (ps ignore the hyperlinks there not supposed to do that)

Thank you all for your help!

Kind Regards

Nick

 

[CajunCenturion]

Have you tried using a single quote [ chr(39) ] instead of a double quote?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[nick262b]

Hi CajunCenturion thanks for the quick reply!

yes Chr(39) works fine and

Cells(1, 1).Value = "<ProjectInput xmlns=" & Chr(39) & "

http://tempuri.org/ProjectInput.xsd"

& Chr(39) & ">"

gives

<ProjectInput xmlns='

http://tempuri.org/ProjectInput.xsd'>

So I dont see why Chr(34) does not work?

Cheers Nick

 

[IRstuff]

This works in the worksheet:
=CONCATENATE("<", CHAR(34), "test", CHAR(34), ">")

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[nick262b]

Hi IRstuff, yeah I know its strange I dont understand why its putting in extra quotation marks (especially at the front?) when I program it in the VBA :S I really dont want to go through and manually change hundereds of files!

Cheers Nick

 

[CajunCenturion]

The forcing of the second quote prevents the ambiguity of how to match up the quotes.
If the line were to read as follows:
"<ProjectInput xmlns="[ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore]">"
The ambiguity is whether the quote that comes immediately after the equal sign is to start a second quote or to close the quote that begins the line. Is this:
"<ProjectInput xmlns=" & "[ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore] & ">"
or is it
"<ProjectInput xmlns= & "[ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore]" & >"
The ambiguity is resolved by using two consecutive quote to signify that the second interpretation is correct.
"<ProjectInput xmlns=""[ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore]"">"

However, it's much easier both to code and to read by using double quotes on the outside and single quotes on the inside, or vise versa, single quotes on the outside and double quotes on the inside.

"<ProjectInput xmlns='[ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore]'>"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Wish we could edit - there is an extra quote on the fifth line, right in front of the http. That line should read

"<ProjectInput xmlns=" & [ignore]

http://tempuri.org/ProjectInput.xsd

[/ignore] & ">"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cowski]

What about this:

Cells(1, 1).Value = "<ProjectInput xmlns=""[URL unfurl="true"]http://tempuri.org/ProjectInput.xsd"">"[/URL]

or, if you are concatenating 3 string variables, try this:

Dim strStart As String
strStart = "<ProjectInput xmlns="
Dim strURL As String
strURL = "[URL unfurl="true"]http://tempuri.org/ProjectInput.xsd"[/URL]
Dim strEnd As String
strEnd = ">"

Cells(1, 1).Value = strStart & Chr(34) & strURL & Chr(34) & strEnd

www.nxjournaling.com

 

[nick262b]

Hi Guys thanks for the responses,

I tried using Cells (1,1) = "<ProjectInput xmlns=""

http://tempuri.org/ProjectInput.xsd"">"

and as you say "" is essentially saying " as text so I would expect to yield
<ProjectInput xmlns="

http://tempuri.org/ProjectInput.xsd">

but it outputted "<ProjectInput xmlns=""

http://tempuri.org/ProjectInput.xsd"">"

Cowski, I tried your code and it also still gave:

"<ProjectInput xmlns=""

http://tempuri.org/ProjectInput.xsd"

">"

It keeps adding the extra bits in red which I dont want.

I though that in general when defining a string the "" marks are ignored i.e.

Cells (1,1) = "string" would give an output of string not "string" so I dont understand where they are coming from.

 

[cowski]

What version of Excel are you using?
I tested my code examples in Excel 2010 and the output to the cell is in the format you wanted (I have not tried exporting these values to a text file).

 

[nick262b]

Hi Cowski,

I am also on excel 2010 just checked and yes in the actual Cell it reads correct as:
<ProjectInput xmlns="

http://tempuri.org/ProjectInput.xsd">

somehow when exporting my worksheet as a text file its adding extra " marks ?

The relvant code for exporting the worksheet is essentially:

Dim ws As String
Dim Output As Workbook

ws = "name of worksheet"

Worksheets(ws).Copy

Set Output = ActiveWorkbook
Output.SaveAs "C:filepath\" & ws & ".extension", xlTextWindows
Output.Close SaveChanges:=False

Cheers,

Nick

 

[CajunCenturion]

The export function is putting the outer quotes around the entire string because the cell contains a string. And because there is a quoted string within that cell, the inner string quotes are being doubled to avoid the ambiguous problem described above.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein