Read Data from Excel File

Hi,
i want to read the data from the attached excel file.
I have problems displaying the turkish chars.
after my import script, all special C and S are converted to plain C and S

any suggestions ?

[Alex]

LxRAE,

Sorry for the delay...

One way to solve the problem is to make sure that Turkish code page is set as default on your Windows. Everything should work OK then.

However if you want to use any other Windows language (e.g. English, Greek, etc) and still want to get Turkish text into CorelDRAW 11, here is one way to do it:

Option Explicit

Private Declare Function WideCharToMultiByte Lib "kernel32" _
            (ByVal CodePage As Long, ByVal dwFlags As Long, _
            ByRef lpWideCharStr As Integer, ByVal cchWideChar As Long, _
            ByVal lpMultiByteStr As String, ByVal cchMultiByte As Long, _
            Optional ByVal lpDefaultChar As Long = 0, _
            Optional ByVal lpUsedDefaultChar As Long = 0) As Long
            
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" _
            (ByVal lpString As String) As Long

Sub ReadExcel()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s As String
    
    ' Open the connection to an Excel file
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\data.xls;" & _
        "Extended Properties=""Excel 8.0; HDR=No;"""

    rs.Open "SELECT * FROM [Tabelle1$a1:a13]", cnn, _
        adOpenForwardOnly, adLockReadOnly
    
    s = ""
    
    While Not rs.EOF
        If s <> "" Then s = s & vbCrLf
        s = s & rs!F1
        rs.MoveNext
    Wend

    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    
    ActiveLayer.CreateArtisticText 0, 10, ConvertToANSI(s, 1254), _
                        cdrTurkish, cdrCharSetTurkish, "Arial"
End Sub

Private Function ConvertToANSI(ByVal s As String, ByVal CodePage As Long) As String
    Dim sa As String
    Dim ln As Long
    Dim str() As Integer
    Dim n As Long
    
    ln = Len(s)
    ReDim str(0 To ln)
    For n = 0 To ln - 1
        str(n) = AscW(Mid$(s, n + 1, 1))
    Next n
    str(ln) = 0
    sa = Space$(ln)
    WideCharToMultiByte CodePage, 0, str(0), ln, sa, ln
    
    ConvertToANSI = Left$(sa, lstrlen(sa))
End Function

I have created a function ConvertToANSI which essentially translates Turkish character from Unicode into the extended character set of the current Windows code page. This tricks CorelDRAW into thinking that everything is OK and by explicitly specifying the language and charset when creating the text, everything looks OK on the screen (make sure you use a Unicode font, such as Arial in the above example).

The above example is also useful in that it shows how to use ADO (ActiveX Data Objects) to access excel file without even launching Excel. (Just make sure you add a reference to "Microsoft ActiveX Data Objects 2.* Library" from your VBA project => Go to Tools>References in CorelDRAW's VBA Editor).

Now, with CorelDRAW 12 everything is so much simpler because Draw 12 is a Unicode application now, so the tricks with playing with codepages is gone now:

Sub ReadExcel()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s As String
    
    ' Open the connection to an Excel file
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\data.xls;" & _
        "Extended Properties=""Excel 8.0; HDR=No;"""

    rs.Open "SELECT * FROM [Tabelle1$a1:a13]", cnn, _
        adOpenForwardOnly, adLockReadOnly
    
    s = ""
    
    While Not rs.EOF
        If s <> "" Then s = s & vbCrLf
        s = s & rs!F1
        rs.MoveNext
    Wend

    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    
    ActiveLayer.CreateArtisticText 0, 10, s, Font:="Arial"
End Sub

(!) THANK YOU SIR - up to now i didnt try the code but i'm sure it will fit.

Originally Posted by LxRAE

Hi,
i want to read the data from the attached excel file.
I have problems displaying the turkish chars.
after my import script, all special C and S are converted to plain C and S

any suggestions ?

SELAM SANIRIM TÜRKSÜNÜZ, BİLGİ ALIŞVERİŞİNDE BULUNMAK İSTERİM. İYİ GÜNLER DİLİYORUM.

muah
buddy, i have no idea what u wan me to tell

: ))) lol.... i thought that you are Turkish, what a fool i am.. anyway no problem pal, enjoy your self. good day...