Non-English characters add '?'s to string variable

[Georgios Liakopoulos]

Hi, I appreciate for reading this
A field of one of my tables contains international names. When I copy
values to a string (mystring = rst![field]) I get a '?' for every
non-English character that exists. Note that these characters appear
fine in the table and any form text boxes that are bound to them. Does
anyone know how can I transfer the text correctly to the string variable?

Regards
Giorgos

 

[Clifford Bass]

Hi Giorgos,

Strings all use Unicode, so the actual characters are stored in the
string. The question mark indicates that it is not able to display the
actual character, perhaps in the current font or perhaps in the current
context. Where is it that you see the question marks?

Clifford Bass

 

[Georgios Liakopoulos]

Hi Clifford and thank you.
Either I copy the string content to the system clipboard (using code)
and then I paste it (manually) somewhere in a Word document or I write
the string content in a txt log file (using code). Even worse, when the
string has '?'s it is not written to the log file but returns an error
message (Run-time error 5: invalid procedure call or argument). I have
tried mystring = StrConv(rst![field], vbUnicode) but this returns "V a l
u e o f F i e l d " instead "Value of field".

Giorgos

Hi Giorgos,

Strings all use Unicode, so the actual characters are stored in the
string. The question mark indicates that it is not able to display the
actual character, perhaps in the current font or perhaps in the current
context. Where is it that you see the question marks?

Clifford Bass

Hi, I appreciate for reading this
A field of one of my tables contains international names. When I copy
values to a string (mystring = rst![field]) I get a '?' for every
non-English character that exists. Note that these characters appear
fine in the table and any form text boxes that are bound to them. Does
anyone know how can I transfer the text correctly to the string variable?

Regards
Giorgos

 

[Clifford Bass]

Hi Giorgos,

What is the code you use to place the information into the clipboard?

Clifford Bass

 

[Clifford Bass]

Hi Giorgos,

Also, what is your code for logging to a file? Are you making sure to
create the file as a Unicode file? Otherwise, it may be creating an ASCII
file, which would not handle the Unicode characters not in the ASCII range.

Clifford Bass

 

[Georgios Liakopoulos]

Hi Clifford, here it is:

The code for the log file is very simple:
*********************************************************
************Log file creation***************************
....
Set fs = CreateObject("Scripting.FileSystemObject")
Set logfile = fs.CreateTextFile("C:\My Documents\Report.log", True)
logfile.writeline ("Start of log file")
....
logfile.writeline (rst![Field])
....
************Log file creation***************************************
*********************************************************


The clipboard code is not mine and I just show you the whole piece:
*********************************************************
************Text to clipboard*****************************************
'<------------------------text2clipboard and clipboard2text
declarations------
Declare Function abOpenClipboard Lib "User32" Alias "OpenClipboard"
(ByVal Hwnd As Long) As Long
Declare Function abCloseClipboard Lib "User32" Alias "CloseClipboard" ()
As Long
Declare Function abEmptyClipboard Lib "User32" Alias "EmptyClipboard" ()
As Long
Declare Function abIsClipboardFormatAvailable Lib "User32" Alias
"IsClipboardFormatAvailable" (ByVal wFormat As Long) As Long
Declare Function abSetClipboardData Lib "User32" Alias
"SetClipboardData" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Declare Function abGetClipboardData Lib "User32" Alias
"GetClipboardData" (ByVal wFormat As Long) As Long
Declare Function abGlobalAlloc Lib "Kernel32" Alias "GlobalAlloc" (ByVal
wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function abGlobalLock Lib "Kernel32" Alias "GlobalLock" (ByVal
hMem As Long) As Long
Declare Function abGlobalUnlock Lib "Kernel32" Alias "GlobalUnlock"
(ByVal hMem As Long) As Boolean
Declare Function abLstrcpy Lib "Kernel32" Alias "lstrcpyA" (ByVal
lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function abGlobalFree Lib "Kernel32" Alias "GlobalFree" (ByVal
hMem As Long) As Long
Declare Function abGlobalSize Lib "Kernel32" Alias "GlobalSize" (ByVal
hMem As Long) As Long
Const GHND = &H42
Const CF_TEXT = 1
Const APINULL = 0
'-------------------------text2clipboard and clipboard2text
declarations----->

'<------------------------text2clipboard function----->
Function Text2Clipboard(szText As String)
Dim wLen As Integer
Dim hMemory As Long
Dim lpMemory As Long
Dim retval As Variant
Dim wFreeMemory As Boolean

' Get the length, including one extra for a CHR$(0) at the end.
wLen = Len(szText) + 1
szText = szText & Chr$(0)
hMemory = abGlobalAlloc(GHND, wLen + 1)
If hMemory = APINULL Then
MsgBox "Unable to allocate memory."
Exit Function
End If
wFreeMemory = True
lpMemory = abGlobalLock(hMemory)
If lpMemory = APINULL Then
MsgBox "Unable to lock memory."
GoTo T2CB_Free
End If

' Copy our string into the locked memory.
retval = abLstrcpy(lpMemory, szText)
' Don't send clipboard locked memory.
retval = abGlobalUnlock(hMemory)

If abOpenClipboard(0&) = APINULL Then
MsgBox "Unable to open Clipboard. Perhaps some other
application is using it."
GoTo T2CB_Free
End If
If abEmptyClipboard() = APINULL Then
MsgBox "Unable to empty the clipboard."
GoTo T2CB_Close
End If
If abSetClipboardData(CF_TEXT, hMemory) = APINULL Then
MsgBox "Unable to set the clipboard data."
GoTo T2CB_Close
End If
wFreeMemory = False

T2CB_Close:
If abCloseClipboard() = APINULL Then
MsgBox "Unable to close the Clipboard."
End If
If wFreeMemory Then GoTo T2CB_Free
Exit Function

T2CB_Free:
If abGlobalFree(hMemory) <> APINULL Then
MsgBox "Unable to free global memory."
End If
End Function
'-------------------------text2clipboard function----->
************Text to clipboard****************************************
*********************************************************

Thank you very much
Giorgos

Hi Giorgos,

Also, what is your code for logging to a file? Are you making sure to
create the file as a Unicode file? Otherwise, it may be creating an ASCII
file, which would not handle the Unicode characters not in the ASCII range.

Clifford Bass

Hi Clifford and thank you.
Either I copy the string content to the system clipboard (using code)
and then I paste it (manually) somewhere in a Word document or I write
the string content in a txt log file (using code). Even worse, when the
string has '?'s it is not written to the log file but returns an error
message (Run-time error 5: invalid procedure call or argument). I have
tried mystring = StrConv(rst![field], vbUnicode) but this returns "V a l
u e o f F i e l d " instead "Value of field".

Giorgos

 

[Clifford Bass]

Hi Giorgos,

I think I can simplify your life.

1) Add another True to the CreateTextFile method:

Set logfile = fs.CreateTextFile("C:\My Documents\Report.log", True, True)

That parameter specifies whether or not to create a Unicode file, with
a default of False.

2) The Text2Clipboard looks at a quick glance like it only places ASCII
text into the clipboard. It could be modified to place Unicode into it. But
there is a better, simpler way. Here is an example:

Public Sub ToTheClipboard()

' Requires "Microsoft Forms 2.0 Object Library"
(c:\windows\system32\fm20.dll)

Dim doWork As New DataObject

doWork.SetText "The symbol for Pi: " & ChrW(&H3A0) & vbCrLf & _
"Greek small letter omega with tonos: " & ChrW(&H3CE)
doWork.PutInClipboard

End Sub

It does require that the "Microsoft Forms 2.0 Object Library" to be
added to your references. While in the VBA Editor go to Tools menu,
References item. If you do not see it in the list to check, browse for the
"fm20.dll" file which you find in your Windows system32 directory (usually
c:\windows\system32 or c:\WINNT\system32).

If you run the above code and then do a paste into Notepad or
something, you should get :

The symbol for Pi: Π
Greek small letter omega with tonos: ÏŽ

So in your code it would look something like this:

Dim doWork As New DataObject

' Other stuff ....

doWork.SetText rst![field]
doWork.PutInClipboard

Hope that helps,

Clifford Bass

Hi Clifford, here it is:

The code for the log file is very simple:
*********************************************************
************Log file creation***************************
....
Set fs = CreateObject("Scripting.FileSystemObject")
Set logfile = fs.CreateTextFile("C:\My Documents\Report.log", True)
logfile.writeline ("Start of log file")
....
logfile.writeline (rst![Field])
....
************Log file creation***************************************
*********************************************************


The clipboard code is not mine and I just show you the whole piece:

[snip]

 

[james]

CAAAAAAAAA

This is funny but I just discovered that [Microsoft Forms 2.0 Object
Library] is loaded!!! I must have missed something. . . Anyway, here
they are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft internet Controls
Adobe Acrobat 8.0 Browser Control Type Library 1.0
Microsoft Word 11.0 Object Library
MouseWheel
Microsoft ActiveX Data Objects 2.6 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Jet and Replication Objects 2.6 Library
Microsoft Shell Controls and Automation
CompatUI 1.0 Type Library
Microsoft Forms 2.0 Object Library

Regards
Giorgos

Hi Giorgos,

Great! Glad to help.

You could be right about it being in some other library and that
crashing things. Or there could be some other incompatibility. I would
be
curious as to which other library would have it. Would you post your
references?

Thanks,

Clifford Bass

 

[Georgios Liakopoulos]

Hi Clifford,
I just finished testing your recommendations. Both the 'True' switch for
the log file and the simplified ToTheClipboard Procedure that you
proposed work perfect! I thank you very much for your help.

Just a note: Surprisingly, the ToTheClipboard procedure does NOT require
the "Microsoft Forms 2.0 Object Library" to work. I found this just
because I unloaded it after getting a crash every time I was trying to
open my .mdb file. I suppose that I already have another object library
in my references that covers the SetText and PutInClipboard methods(?).
Actually, this may be the reason for the crashes.

Regards, Giorgos

Hi Giorgos,

I think I can simplify your life.

1) Add another True to the CreateTextFile method:

Set logfile = fs.CreateTextFile("C:\My Documents\Report.log", True, True)

That parameter specifies whether or not to create a Unicode file, with
a default of False.

2) The Text2Clipboard looks at a quick glance like it only places ASCII
text into the clipboard. It could be modified to place Unicode into it. But
there is a better, simpler way. Here is an example:

Public Sub ToTheClipboard()

' Requires "Microsoft Forms 2.0 Object Library"
(c:\windows\system32\fm20.dll)

Dim doWork As New DataObject

doWork.SetText "The symbol for Pi: " & ChrW(&H3A0) & vbCrLf & _
"Greek small letter omega with tonos: " & ChrW(&H3CE)
doWork.PutInClipboard

End Sub

It does require that the "Microsoft Forms 2.0 Object Library" to be
added to your references. While in the VBA Editor go to Tools menu,
References item. If you do not see it in the list to check, browse for the
"fm20.dll" file which you find in your Windows system32 directory (usually
c:\windows\system32 or c:\WINNT\system32).

If you run the above code and then do a paste into Notepad or
something, you should get :

The symbol for Pi: Ð
Greek small letter omega with tonos: þ

So in your code it would look something like this:

Dim doWork As New DataObject

' Other stuff ....

doWork.SetText rst![field]
doWork.PutInClipboard

Hope that helps,

Clifford Bass

Hi Clifford, here it is:

The code for the log file is very simple:
*********************************************************
************Log file creation***************************
....
Set fs = CreateObject("Scripting.FileSystemObject")
Set logfile = fs.CreateTextFile("C:\My Documents\Report.log", True)
logfile.writeline ("Start of log file")
....
logfile.writeline (rst![Field])
....
************Log file creation***************************************
*********************************************************


The clipboard code is not mine and I just show you the whole piece:

[snip]

 

[Clifford Bass]

Hi Giorgos,

Great! Glad to help.

You could be right about it being in some other library and that
crashing things. Or there could be some other incompatibility. I would be
curious as to which other library would have it. Would you post your
references?

Thanks,

Clifford Bass

 

[Clifford Bass]

Hi Giorgos,

Thanks much! I added all into a new database except for Microsoft
Internet Controls and MouseWheel, which I do not appear to have. And except
for Microsoft Forms 2.0 Object Library. Then tried a compile. The compile
failed. So, it looks pretty likely it is only in the MS Forms 2.0 item.
Again, thanks!

Clifford Bass

 

[hor vannara]

This is funny but I just discovered that [Microsoft Forms 2.0 Object
Library] is loaded!!! I must have missed something. . . Anyway, here
they are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft internet Controls
Adobe Acrobat 8.0 Browser Control Type Library 1.0
Microsoft Word 11.0 Object Library
MouseWheel
Microsoft ActiveX Data Objects 2.6 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Jet and Replication Objects 2.6 Library
Microsoft Shell Controls and Automation
CompatUI 1.0 Type Library
Microsoft Forms 2.0 Object Library

Regards
Giorgos

Hi Giorgos,

Great! Glad to help.

You could be right about it being in some other library and that
crashing things. Or there could be some other incompatibility. I would
be
curious as to which other library would have it. Would you post your
references?

Thanks,

Clifford Bass