Excel recordset encoding issue-CodePudding

I am having issue where recordset doesn't display words correctly.

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Dim strCon As String
strCon = "Driver={Oracle in instantclient_21_6};Dbq=DAB;User Id=USER;Password=PASSWORD;"
Conn.Open (strCon)

This part is reading query which is written into txt file (txt file is in UTF-8 encoding):

fileSpec = "\\192.168.0.7\...\saldo_kartica.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
caseInfo = objTS.ReadAll
caseInfo = Replace(caseInfo, "$ceid", caseID)
objTS.Close

cmdInfo.CommandType = ADODB.CommandTypeEnum.adCmdText
cmdInfo.ActiveConnection = Conn
cmdInfo.CommandText = caseInfo

Set rsInfo = New ADODB.Recordset
Set rsInfo = cmdInfo.Execute

And values from rsInfo displays text like this:

but is should be displayed as: "Plaćeno"

CodePudding user response：

I found solution, basically txt files were using UTF-8 encoding. I had to change file encoding to ANSI (simple save as using ANSI).

And changed connection string to this:

strCon = "Driver={Oracle in instantclient_21_6};Dbq=DAB;User Id=USER;Password=PASSWORD;FWC=T;"

I added FWC=T which forces SQL_WCHAR Support https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1182

Solution was found overhere: https://www.access-programmers.co.uk/forums/threads/oracle-linked-table-display-unicode-chars-in-datasheet.185173/

Hope this will help to somebody else also. :)