vba replacing two quotation marks with one / generating txt files

Question

I have created a macro that generates txt files with data from every row in excel. I have problem with " (quotation marks)

Here is a line from VBA that makes problems:

wsTemp.Cells(6, 1).Value = "The ""Number"" is " & wsSource.Cells(r, 3).Value

I need this: The "Number" is MYNUMBER

Instead of i get this: " The ""Number"" is" MYNUMBER

This does not work either:

wsTemp.Cells(6, 1).Value = "The" & Chr(34) & "Number"" is " & Chr(34) & wsSource.Cells(r, 3).Val (got the same output)

I tried replacing "" with Chr(34) (didn't worked)

I also tried replace function at the end:

wsTemp.Cells(6, 1).Value = Replace(wsTemp.Cells(6, 1).Value, """", """) (didn't worked) and wsTemp.Cells(6, 1).Value = Replace(wsTemp.Cells(6, 1).Value, """", Chr(34)) (didn't worked as well)

Replace() does works with other Chr() or with Replace(wsTemp.Cells(6, 1).Value, """", "") but not with Chr(34)

Anyway, i need one ", not two ' or anything else

I am saving generated files as xlCurrentPlatformText and .txt, I have also tried other formats

Also, I have noticed this when I manually saved the same text from excel as txt

I have tried almost everything and nothing worked

I am using Win 7 and Excel 2013

Answer 1

So i did it as @xidgel suddgested, with FindAndReplaceText (fart)

I replaced all " with $$$$

I created a file replace.bat

fart -r "MYLOCATION*.txt" $$$$ \"

And added shell execute command at the end of vba macro

Call Shell("C:\LOCATION\replace.bat", vbNormalFocus)