Replacing single apostrophe (') with two single quotes ('')

NIDU

unread,

Mar 17, 2008, 5:12:27 AM3/17/08

to

I am using RC wintrans as the software to localize our company`s
software. To localize the strings being used in our software i export
all the strings with their string IDs to an excel file and send them
for translation. Now when i try to import the translation back into
the DB of RC wintrans, excel does not return the strings starting with
single quote ' , hence the translation of such strings are not
imported automatically into RC wintrans.

I would like to write a script, which searches through the strings in
the excel file and replace every string starting with single quote '
with two single quotes '' ( remind you its not double quote but two
single quotes).

Can anybody help me with that? Can you please share it with me if you
have any script that could help.

NIDU

unread,

Mar 17, 2008, 6:14:08 AM3/17/08

to

The script that is used to export RC wintTrans ID`s and Source text to
a text file is

Sub ScriptHandler()

DBFileName = ProjectDoc.GetCurTranslDoc
If (Len(DBFileName) > 0) Then

' Open an empty text document window
TextDoc.New

' Get the target languages
Dim LangIDAry
nLang = ProjectDoc.GetTargetLanguages( LangIDAry)

' Get all items
Dim ItemIndexAry
nItems = ProjectDoc.GetTextItemsIndex(ItemIndexAry, 0, 0)
TextDoc.Write("Translation database: " & DBFileName)
TextDoc.Write("Number of text items: " & nItems)

' For all items
For iItem = 0 To nItems-1

Dim ObjType, ObjMainResID, ObjResID, Text
bRet = ProjectDoc.GetResObjectData(ItemIndexAry(iItem), ObjType,

ObjMainResID, ObjResID)
Text = ProjectDoc.Text(ItemIndexAry(iItem), 0)

TextDoc.Write("")
TextDoc.Write("No. " & iItem)
TextDoc.Write("ID=" & ObjResID)
TextDoc.Write("Source Text=" & """" & Text & """")

' For all languages
For iLang= 0 To nLang-1
LangStr = Utility.GetLanguageStr(LangIDAry(iLang))
Text = ProjectDoc.Text(ItemIndexAry(iItem), LangIDAry(iLang))
TextDoc.Write(LangStr & "=" & """" & Text & """")
Next

Else
bRes = Appl.FlashInfo("No current document!", 1)
End If

End Sub'

Now i would like to change this script in such a way that it exports
to an excel file ( Titles: ID & Source Text), Secondly the script
searches for the strings under `Source text` and replaces all the
strings which starts with single quote ` with two single quotes ''.
Please HELP!

Corey Thomas - MCSE/MCSA/MCDBA

unread,

Mar 17, 2008, 9:33:04 AM3/17/08

to

You could do it with a script, but you could do it just as fast in Excel if
all the data needs double single quotes before and after.

First do a replace to remove all the current ones. The use the concatenate
formula in Excell. It will add them in for ya.

Corey Thomas - MCSE/MCSA/MCDBA

unread,

Mar 17, 2008, 9:41:00 AM3/17/08

to

Well, since you already have a script... ;)

Go here for examples on using Excel in vbscript:
http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/#ADSC.htm

To change the quotes, you have a few options. You could use Replace()
function.

Example:

newString = Replace(oldString, "'", "''")

Note: There is one single quote between double quotes and two single quotes
between double quotes.

The problem with this method is that you may run into issues if there are
already two single quotes source. To get around that, you'd have to do an
inStr() search to find the first one, then get the next character to see if
it's a single quote.

I'm not sure of your data source, but here are the two methods you can use.
Also, why are you using two single quotes instead of one double quote?

-Corey

NIDU

unread,

Mar 28, 2008, 10:59:39 AM3/28/08

to

On Mar 17, 6:41 pm, Corey Thomas - MCSE/MCSA/MCDBA

<CoreyThomasMCSEMCSAMC...@discussions.microsoft.com> wrote:
> Well, since you already have a script... ;)
>

> Go here for examples on using Excel in vbscript:http://www.activexperts.com/activmonitor/windowsmanagement/scripts/ms...

> > Please HELP!- Hide quoted text -
>
> - Show quoted text -

Sorry for the late reply. Thanks alot Corey. The Replace() function
and the link you gave me to use excel in vbscripts really helped.
To anser your question ,i-e why i am using two single quotes instead
of one double quote?

The reason is that EXCEL does not return the character ' (0x27) if
it is the first character in the string.

Technical background:

RC-Win Trans uses COM automation (Excel as COM server) to read the
text from
the XLS document. While, getting the text from the xls document EXCEL
does not return
the character ' if it is the first character in the string.

RC-Win Trans Import:

RC-Win Trans compares the source text in RC-Win Trans (translation
project)
and the source text in the XSL document to import a translation text.
Because Excel
does not return the character ' the both source text are different
and RC-Win Trans does
not import the translation text.
This happens for all items where the source text has a ' (0x27) as the
first character.

This is difficult to understand. But RC-Win Trans has no solution for
this problem since Excel forces this problem.

If i replace a single quote with double quote, the comparison will
fail and the translation against that string in the next column in
excel will not be updated to my RC win trans DB

if i replace a single quote with two single quotes, while importing
the translation from excel file, excel will not return the first
single quote. The rest of the text ( starting with one single quote)
will be the same and no error will occur.

The script that i posted previously exports the translation DB
( Titles: IDs, Source Text, German, Spanish) to a text file. With your
help now it exports the same to an excel file and while doing that
wherever it finds a single quote in a string (under `source text`)
replaces it with two single quotes. In such a way when i import the
same excel file (after translation strings are added), no error will
occur.

I used the replace function as Text = Replace(mid(text,1,1), "'" ,
"''",1,1,1) & mid(text,2) which worked.

Just FYI.. my previous script now looks like this

'Name the script

Sub ScriptHandler()

' Take the file path of the data base Project

DBFileName = ProjectDoc.GetCurTranslDoc

'If the lengh of the characters of DB file path is greator than 0,
Then proceed other wise go to Else

If (Len(DBFileName) > 0) Then

' Open a new excel file

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

'Print ID in cell (1,1) as the title, fontsize=15, BOLD, Interior
color is grey

objExcel.Cells(1, 1).Value = "ID"

objExcel.Cells(1,1).Font.size=15

objExcel.cells(1,1).Font.bold=TRUE

objExcel.cells(1,1).interior.colorindex=48

objExcel.cells(1,1).columnwidth=15

'Print Source Text in cell (1,2) as the Title, fontsize=15,BOLD,
interior color is grey

objExcel.Cells(1,2).Value="Source Text"

objExcel.cells(1,2).Font.size=15

objExcel.cells(1,2).Font.bold=TRUE

objExcel.cells(1,2).interior.colorindex=48

objExcel.cells(1,2).columnwidth=60

' Get the target languages. Return Value: The number of target
languages

Dim LangIDAry

nLang = ProjectDoc.GetTargetLanguages( LangIDAry)

' Get all items. Return Value: Number of items. The value is equal to
the size of the array returned.

Dim ItemIndexAry

nItems = ProjectDoc.GetTextItemsIndex(ItemIndexAry, 0, 0)

' For all items

For iItem = 0 To nItems-1

Dim ObjType, ObjMainResID, ObjResID, Text

bRet = ProjectDoc.GetResObjectData(ItemIndexAry(iItem), ObjType,
ObjMainResID, ObjResID)

Text = ProjectDoc.Text(ItemIndexAry(iItem), 0)

' Start printing the ResId from cell (3,1) onwards

objExcel.cells(iItem+3,1).Value=objResID

'Add the replace function in this position. i-e Replace every string
which starts with one single quote with two single quotes (only in the
begining)

'Text = Replace(Text, "'" , "''",
1,1,1) ' This function will
create problems for strings like Blue'Green'Brown which will
become Blue''Green'Brown ,thats why wont use

Text = Replace(mid(text,1,1), "'" , "''",1,1,1) & mid(text,2)

objExcel.cells(iitem+3,2).Value=Text

' For all languages

For iLang= 0 To nLang-1

LangStr = Utility.GetLanguageStr(LangIDAry(iLang))

'Print the Name of the Language string e.g German, Spanish

objExcel.Cells(1,iLang+3).Value=LangStr

objExcel.cells(1,iLang+3).Font.size=15

objExcel.cells(1,ilang+3).Font.bold=TRUE

objExcel.cells(1,ilang+3).interior.colorindex=48

objExcel.cells(1,ilang+3).columnwidth=60

Text = ProjectDoc.Text(ItemIndexAry(iItem), LangIDAry(iLang))

objExcel.cells(iItem+3,iLang+3)=Text

Else

bRes = Appl.FlashInfo("No current document!", 1)
End If

End Sub'

TASK ACCOMPLISHED..... Thanks indeed for your help.. really !