NIDU
Fri Mar 28 09:59:39 CDT 2008
On Mar 17, 6:41=A0pm, Corey Thomas - MCSE/MCSA/MCDBA
<CoreyThomasMCSEMCSAMC...@discussions.microsoft.com> wrote:
> Well, since you already have a script... =A0;)
>
> Go here for examples on using Excel in vbscript:
http://www.activexperts.co=
m/activmonitor/windowsmanagement/scripts/ms...
>
> To change the quotes, you have a few options. =A0You could use Replace()
> function.
>
> Example:
>
> newString =3D Replace(oldString, "'", "''") =A0
>
> Note: There is one single quote between double quotes and two single quote=
s
> between double quotes.
>
> The problem with this method is that you may run into issues if there are
> already two single quotes source. =A0To get around that, you'd have to do =
an
> inStr() search to find the first one, then get the next character to see i=
f
> it's a single quote.
>
> I'm not sure of your data source, but here are the two methods you can use=
. =A0
> Also, why are you using two single quotes instead of one double quote?
>
> -Corey
>
>
>
> "NIDU" wrote:
> > On Mar 17, 2:12 pm, NIDU <nid...@hotmail.com> wrote:
> > > 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.
>
> > The script that is used to export RC wintTrans ID`s and Source text to
> > a text file is
>
> > Sub ScriptHandler()
>
> > DBFileName =3D ProjectDoc.GetCurTranslDoc
> > If (Len(DBFileName) > 0) Then
>
> > ' =A0Open an empty text document window
> > =A0 TextDoc.New
>
> > ' =A0Get the target languages
> > =A0 Dim LangIDAry
> > =A0 nLang =3D ProjectDoc.GetTargetLanguages( LangIDAry)
>
> > ' =A0Get all items
> > =A0 Dim ItemIndexAry
> > =A0 nItems =3D ProjectDoc.GetTextItemsIndex(ItemIndexAry, 0, 0)
> > =A0 TextDoc.Write("Translation database: " & DBFileName)
> > =A0 TextDoc.Write("Number of text items: " & nItems)
>
> > ' =A0For all items
> > =A0 For iItem =3D 0 To nItems-1
>
> > =A0 =A0 Dim ObjType, ObjMainResID, ObjResID, Text
> > =A0 =A0 bRet =3D ProjectDoc.GetResObjectData(ItemIndexAry(iItem), ObjTyp=
e,
>
> > ObjMainResID, ObjResID)
> > =A0 =A0 Text =3D ProjectDoc.Text(ItemIndexAry(iItem), 0)
>
> > =A0 =A0 TextDoc.Write("")
> > =A0 =A0 TextDoc.Write("No. " & iItem)
> > =A0 =A0 TextDoc.Write("ID=3D" & ObjResID)
> > =A0 =A0 TextDoc.Write("Source Text=3D" & """" & Text & """")
>
> > =A0 =A0 ' For all languages
> > =A0 =A0 For iLang=3D 0 To nLang-1
> > =A0 =A0 =A0 =A0LangStr =3D Utility.GetLanguageStr(LangIDAry(iLang))
> > =A0 =A0 =A0 =A0Text =3D ProjectDoc.Text(ItemIndexAry(iItem), LangIDAry(i=
Lang))
> > =A0 =A0 =A0 =A0TextDoc.Write(LangStr & "=3D" & """" & Text & """")
> > =A0 =A0 Next
>
> > =A0 Next
>
> > =A0 bRes =3D Appl.FlashInfo("Finished!", 0)
>
> > Else
> > =A0 bRes =3D 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!- 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 =3D 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 =3D 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 =3D CreateObject("Excel.Application")
objExcel.Visible =3D True
objExcel.Workbooks.Add
'Print ID in cell (1,1) as the title, fontsize=3D15, BOLD, Interior
color is grey
objExcel.Cells(1, 1).Value =3D "ID"
objExcel.Cells(1,1).Font.size=3D15
objExcel.cells(1,1).Font.bold=3DTRUE
objExcel.cells(1,1).interior.colorindex=3D48
objExcel.cells(1,1).columnwidth=3D15
'Print Source Text in cell (1,2) as the Title, fontsize=3D15,BOLD,
interior color is grey
objExcel.Cells(1,2).Value=3D"Source Text"
objExcel.cells(1,2).Font.size=3D15
objExcel.cells(1,2).Font.bold=3DTRUE
objExcel.cells(1,2).interior.colorindex=3D48
objExcel.cells(1,2).columnwidth=3D60
' Get the target languages. Return Value: The number of target
languages
Dim LangIDAry
nLang =3D 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 =3D ProjectDoc.GetTextItemsIndex(ItemIndexAry, 0, 0)
' For all items
For iItem =3D 0 To nItems-1
Dim ObjType, ObjMainResID, ObjResID, Text
bRet =3D ProjectDoc.GetResObjectData(ItemIndexAry(iItem), ObjType,
ObjMainResID, ObjResID)
Text =3D ProjectDoc.Text(ItemIndexAry(iItem), 0)
' Start printing the ResId from cell (3,1) onwards
objExcel.cells(iItem+3,1).Value=3DobjResID
'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 =3D 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 =3D Replace(mid(text,1,1), "'" , "''",1,1,1) & mid(text,2)
objExcel.cells(iitem+3,2).Value=3DText
' For all languages
For iLang=3D 0 To nLang-1
LangStr =3D Utility.GetLanguageStr(LangIDAry(iLang))
'Print the Name of the Language string e.g German, Spanish
objExcel.Cells(1,iLang+3).Value=3DLangStr
objExcel.cells(1,iLang+3).Font.size=3D15
objExcel.cells(1,ilang+3).Font.bold=3DTRUE
objExcel.cells(1,ilang+3).interior.colorindex=3D48
objExcel.cells(1,ilang+3).columnwidth=3D60
Text =3D ProjectDoc.Text(ItemIndexAry(iItem), LangIDAry(iLang))
objExcel.cells(iItem+3,iLang+3)=3DText
Next
'objExcel.cells.Entirecolumn.columnwidth=3D30 ' Set the
entire column of the spread sheet , currently tagged out
'objExcel.Cells.EntireColumn.AutoFit '
Auto fit the entire column .currently tagged out
Next
bRes =3D Appl.FlashInfo("Finished!", 0)
Else
bRes =3D Appl.FlashInfo("No current document!", 1)
End If
End Sub'
TASK ACCOMPLISHED..... Thanks indeed for your help.. really !