Using Excel 2007 with different charsets - UniCode?

[Joost Kant]

I have an Excel 2007 spredsheet that contains multi language text, most common languages, Dutch, English, French, Hungarian, but also Greec text.

I want to export the contens of this spreadsheet into different database tables (MySQL). For now, without Greec, I manage the spreadsheetdata and save it as to several Tab delimited tables. But the Greec text changes into ask-signs. How can I save it with the powerbasic console compiler into multilanguage files in for instance Unicode text tables?

[Michael Mattias]

I don't know the COM interface very well, although I do use it to export Excel sheets to tab-delimited text files. (Code is here somewhere, I just copied it and use it).

I'll bet there is some method you can call or value to set to set the text type and/or character set for export to text.. or maybe you will have to get each cell independently and convert it yourself....

.... if you get Unicode Out, you can convert to/from just about anything using the WideCharToMultiByte and/or MultiByteToWideChar Windows' API functions.

[Joost Kant]

Micheal, I'am using it for the same purpose, but now I can't use it because the Greec texts are not Greek anymore in tab-delimited text. I need to know more about all the functions reading cells out of an Excel spreadsheet. When I read the Greec cell and watch the variable (Variant) I only see some Greec chars but merely ask-signs. The type of this Variant is 8 = Dynamic string. I would have it to be Unicode string (31) or perhaps Ansi string (30). How can I change an Excel Cell-type? When I copy the Excel cell and paste it into Notepad and save the textfile I get the warning that it contains Unicode and have to be saved as an Unicode textfile.

I can solve the problem when I can save the spreadsheet in Unicode text from within a PwrBasic Console application.

[José Roca]

The type of this Variant is 8 = Dynamic string. I would have it to be Unicode string (31) or perhaps Ansi string (30).

8 (%VT_BSTR) is the correct type: a dynamic unicode string. Your problem is that if you use VARIANT$ to extract the contents it is converted to ansi. You can use the following function to extract the contents in unicode format.

FUNCTION VAR2BSTR (BYVAL vVar AS VARIANT) AS STRING

   LOCAL s AS STRING
   LOCAL l AS LONG
   LOCAL pv AS VARIANTAPI PTR

   IF VARIANTVT(vVar) <> %VT_BSTR THEN EXIT FUNCTION
   pv = VARPTR(vVar)
   l = lstrlenW(BYVAL @pv.vd.bstrVal)
   s = PEEK$(@pv.vd.bstrVal, l * 2)
   FUNCTION = s

END FUNCTION

[Joost Kant]

Thanks Jose, My problem is solved!