AHK not copying '$' as text Topic is solved

[zvit]

My AHK code loops through an Excel named range and adds its values to a variable which I then output to a file.
There is one cell that contains a dollar sign in the text ($) that the exported file is not adding. It's also emitting the first number after the sign.

This is the original string in the range:
"Pfizer, BioNTech, and Moderna make a combined $65,000 per minute profit"

And this is what my code is writing to the file:
"Pfizer, BioNTech, and Moderna make a combined 5,000 per minute profit"

Is there a function that I can use to force AHK to see the entire string as text?

Here's my code:

Code: Select all

MyPath := "F:\AHK\"
FilePath := "F:\AHK\Words.xlsm"

    if ErrorLevel
      return
    else
	{
		xlApp := ComObjCreate("Excel.Application")
		xlWB := xlApp.Workbooks.Open(FilePath,0,0)
		xlApp.Visible := false
		xlWS := xlApp.Sheets("USED")
		xlApp.Range("rng_HELPER_SORT").Sort(xlApp.Range("A1"),,,,,,,1) ; with Header
		
		HtmlArr := xlApp.Range("rng_HTML")

		for cell in HtmlArr
		{
			dataPuzNum.= cell.Offset(0, -8).text "`n"
			dataHTML.= cell.text "`n"
		}

	FileRead, OutputVar, %MyPath%003_13x13.cfp
	NewValPuzNum := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . dataPuzNum)
	NewValHTML := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . dataHTML)
	FileAppend, %NewValPuzNum%, %MyPath%TmpPuzNum.txt
	FileAppend, %NewValHTML%, %MyPath%TmpHTML.txt 
	}

done:
;xlWB.Save()
xlWB.Close(0) ;SaveChanges:=False
xlApp.Quit()
xlApp := xlWB := ""
msgbox, finished
ExitApp

[mikeyww]

Check the variable values at every step in your script, so that you can pinpoint the step where the characters are dropped.

[zvit]

Check the variable values at every step in your script, so that you can pinpoint the step where the characters are dropped.

Interesting thought. I didn't think to check this because I thought, "I already know the value from the file"
But with a msgbox I see that the value is getting put into the array correctly:



So the issue must be in the FileAppend - that it's not using a correct encoding for the text file. I'll have to look into this. Any ideas?
I tried to put UTF-8 and UTF-8-RAW as the encoding parameter in FileAppend but it didn't help.

[boiler]

How about checking your NewVal variables with a MsgBox right before the FileAppend?

[zvit]

How about checking your NewVal variables with a MsgBox right before the FileAppend?

Was just doing that before I came to check here. That's where the issue is! The RegExReplace is messing it up.

Here's my code:

Code: Select all

    NewValHTML := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . dataHTML)
    FileAppend, %NewValHTML%, %SrcPath%TmpHTML.txt

The dataHTML var has the correct text. The NewValHTML is getting the issue because of the RegExReplace. RegEx is a pain in the you-know-what, so I'll have to figure out what to change.
I'll explain in a few minutes what I use the RegExReplace for.

[zvit]

I have a text file that contains the following text:

Code: Select all

<VERSION>1</VERSION>
<TITLE/>
<AUTHOR/>
<COPYRIGHT/>
<WORDS>
<WORD dir="DOWN" id="34" isTheme="false" num="4"/>
</WORDS>
<NOTES/>

My code uses RegExReplace to replace the rows in between the <WORDS> and </WORDS> tags.
So after my code runs, the file SHOULD read as so:

Code: Select all

<VERSION>1</VERSION>
<TITLE/>
<AUTHOR/>
<COPYRIGHT/>
<WORDS>
<WORD dir="DOWN" id="34" isTheme="false" num="4">COVID-19 vaccine ______cturers Pfizer, BioNTech, and Moderna make a combined $65,000 per minute profit</WORD>
</WORDS>
<NOTES/>

But as I said, the RegExReplace is changing $65,000 to 5,000 - deleting the $6.

[mikeyww]

You can also post the exact value of dataHTML, not as you think it is, but as your script reports it.

[zvit]

You can also post the exact value of dataHTML, not as you think it is, but as your script reports it.

Not sure I understand.
As I said, dataHTML has the correct text, including the $6. So if I output dataHTML to a file, it's fine.
I now have to figure out how to get the dataHTML value into the file, in between the <WORDS> and </WORDS> tags, while of course, not deleting the $6.

I'm reading about escaping a dollar sign with a double $$ here: https://www.autohotkey.com/docs/commands/RegExReplace.htm but I'm not sure this applies to me.

[boiler]

Yes, wherever you have a $ in dataHTML, you need to replace it with $$:

Code: Select all

    NewValHTML := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . StrReplace(dataHTML, "$", "$$"))

[zvit]

Yes, wherever you have a $ in dataHTML, you need to replace it with $$:

Code: Select all

    NewValHTML := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . StrReplace(dataHTML, "$", "$$"))

I will try this now. But in the meantime, if anyone wants to play around with this issue, just use this short code:

Code: Select all

str1 := "Wow! This is worth 0 which is a lot!"
str2 := "$65,000"
msgbox % RegExReplace(str1, "s) worth \K.*?(?= which)", str2)
ExitApp

This is the msgbox result:

[boiler]

This gives the expected result:

Code: Select all

str1 := "Wow! This is worth 0 which is a lot!"
str2 := "$65,000"
msgbox % RegExReplace(str1, "s) worth \K.*?(?= which)", StrReplace(str2, "$", "$$"))
ExitApp

[zvit]

Yes, wherever you have a $ in dataHTML, you need to replace it with $$:

Code: Select all

    NewValHTML := RegExReplace(OutputVar, "s)<WORDS>\K.*?(?=</WORDS>)", "`n" . StrReplace(dataHTML, "$", "$$"))

Worked! I was reading about the $$ but couldn't figure out how to apply it.
Thanks to everyone that helped!

[mikeyww]

Yes, I think it's simply because the dollar sign has special meaning in the regex replacement string.

[boiler]

Yes, it wants to replace it with the 6th matching sub-pattern (in the case of $65,000) because it sees $6, which there isn't one, so it puts nothing in its place.