Visual Basic

Re: Replacing & during conversion of Excel to XML using VBA

20-Jul-10 6:01

Hi Friends,

I am converting an excel to xml using vba. THere this excel data having &. But when converting & to xml it is giving error. So I am using the following funtion to replace &.

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Integer
' replace Ampersands (&) with plus symbols (+)
MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
Mid(AnyStr, MyPos, 1) = "+"
MyPos = InStr(1, AnyStr, "&")
Loop
End

But the above function replacing with +. But I wanted to display in the browser as & only. Kindly Help.

Regards,
Priya.

riced20-Jul-10 6:53

riced

20-Jul-10 6:53

Would using & fix the problem?

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Re: Replacing & during conversion of Excel to XML using VBA

priyaahh20-Jul-10 20:04

Re: Replacing & during conversion of Excel to XML using VBA

20-Jul-10 20:04

HI

Thanks. I have not tried it ..let me try and reply

Thanks,
Priya.

priyaahh21-Jul-10 1:24

Re: Replacing & during conversion of Excel to XML using VBA

21-Jul-10 1:24

Hi,

& is not working. Its going into infinitely....Any other approach to implement this?

Thanks,
Priya.

DaveAuld20-Jul-10 7:09

DaveAuld

20-Jul-10 7:09

Forgot how to use code blocks already?

Did you get your previous xml problem working in the end, didn't hear any more in the thread?

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Re: Replacing & during conversion of Excel to XML using VBA

priyaahh20-Jul-10 20:02

Re: Replacing & during conversion of Excel to XML using VBA

20-Jul-10 20:02

Hi Dave,

Thanks. I think you are talking abt the function i ve written..without returning that function..with function name..

I have that line in my code..since it is big code i just copy pasted only first part and end function missed to paste last to previus line.

Here is the function:

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Integer
' replace Ampersands (&) with plus symbols (+)
MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
Mid(AnyStr, MyPos, 1) = "+"
MyPos = InStr(1, AnyStr, "&")
Loop

RemoveAmpersands = AnyStr
End Function

If you are trying to say something pls reply..

Thanks
Priya

AnnieMacD20-Jul-10 13:27

AnnieMacD

20-Jul-10 13:27

priyaahh wrote:
I am using the following funtion to replace &.

Your code replaces all the ampersands (&) in a string with plus (+) which is what you say you want to do.

Then you say:

priyaahh wrote:
But the above function replacing with +. But I wanted to display in the browser as & only.

...so, what exactly do want to achieve?

BTW this would do the same thing in one line.

Dim newStr As String = AnyStr.Replace("&", "+")

It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca

Re: Replacing & during conversion of Excel to XML using VBA

priyaahh20-Jul-10 20:00

Re: Replacing & during conversion of Excel to XML using VBA

20-Jul-10 20:00

Hi
Thanks. Yes my code is replacing & with +. Because if i display as & in XML it is giving error. But i wanted to display & as & not as +. Is there any wy to display & in XML browser.

Regards,
Priya.

AnnieMacD21-Jul-10 2:42

AnnieMacD

21-Jul-10 2:42

Then riced's answer (above) is what you need. I found this with Google http://articles.techrepublic.com.com/5100-10878_11-5032714.html[^]

Here's an extract:

Ampersand—&—&
greater-than—>—>
less-than—<—<
apostrophe—'—'
quote—"—"

If I may say so, you seem to make a lot of work for yourself!

It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca

Re: Replacing & during conversion of Excel to XML using VBA

priyaahh21-Jul-10 23:36

Re: Replacing & during conversion of Excel to XML using VBA

21-Jul-10 23:36

Hi Annie,

Thanks for your reply.

I tried using & but when i use this, I am not getting any output and the application is processing.....and not stopping..I have to use TaskManager and End the application.

Kindly help.

Regards,
Priya.

AnnieMacD22-Jul-10 4:34

AnnieMacD

22-Jul-10 4:34

OK, here are two code snippets that illustrate what you need.

<p>
       You can find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"
           title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>.
   </p>

After the 152368 you see ampersand(&), letter a, letter m, letter p then semi-colon(;)? That is what is required in place of the &. This will do the replacement for you:

Dim newStr As String = oldStr.Replace("&", "&amp;")

It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca

IIF strange behaviour

ddecoy19-Jul-10 21:59

19-Jul-10 21:59

Dim o = IIf(True, "1", CType("", Decimal))

This gives you an invalid cast exception.
weak..

DaveAuld19-Jul-10 22:52

DaveAuld

19-Jul-10 22:52

i always wanted to know that...................... Unsure | :~

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Luc Pattyn19-Jul-10 23:12

Luc Pattyn

19-Jul-10 23:12

I bet you still want to know why that happens?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Dalek Dave19-Jul-10 23:28

Dalek Dave

19-Jul-10 23:28

Tune in next week to see the next thrilling installment...

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

ddecoy20-Jul-10 0:10

20-Jul-10 0:10

You bet...

Dave Kreskowiak20-Jul-10 2:04

Dave Kreskowiak

20-Jul-10 2:04

Yeah, and ....?? It doesn't surprise me in the least.

You cannot convert String.Emtpy to a Decimal type and, the way you coded it, IIF can either return a String ("1") or Decimal. The compiler can't figure out what the primary type of the return value is supposed to be and there is no implicit conversion available to go from String to Decimal. If you has IIF returning two different numeric types, say Integer and Short, this wouldn't be a problem because a Short can be upsized into an Integer, so the return type for IIF would be Integer.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

ddecoy20-Jul-10 2:11

20-Jul-10 2:11

So why can I compile this then?
In c# you can't...

Luc Pattyn20-Jul-10 2:15

Luc Pattyn

20-Jul-10 2:15

C# is a real programming language.
VB/VB.NET isn't by default; you can improve things a bit by starting with OPTION STRICT ON.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Eddy Vluggen20-Jul-10 2:27

Eddy Vluggen

20-Jul-10 2:27

Does C# have a CType[^] function? I'd guess that C# checks your cast at compiletime, whereas the CType function may throw runtime errors.

They're not the same languages, so behaviour doesn't have to be similar Smile | :)

I are Troll Suspicious | :suss:

ddecoy20-Jul-10 3:14

20-Jul-10 3:14

I thougth IIF was something special, like if the condition is true ,the false part is never reached.
But it's nothing more than this:

Public Function IIF(ByVal condition As Boolean, ByVal truepart As Object, ByVal falsepart As Object) As Object
        If condition Then
            Return truepart
        Else
            Return falsepart
        End If
    End Function