Cannot display non-English characters in Excel

  • Thread starter Thread starter Terence
  • Start date Start date
T

Terence

Dear all,

I have an Excel macro which has a command button on the
worksheet (Sheet1). When the button is clicked, the
program retrieves data from SQL server (there are some
Chinese characters in the data) and puts the data on
Sheet2. The data is retrieved by using "QueryTable" in my
coding. However, the Chinese data cannot be displayed but
it shows mess codes instead, even the program is run in
Chinese Windows 2000. Can somebody help? I am so
frustrated. Thanks a lot.

The program is as follows:
' ****************************************************
Sub Button_Click()
Dim cnn1 As ADODB.Connection
Dim rstTmp As ADODB.Recordset
Dim mySQL As String

Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=User;Password=Password;Initial
Catalog=myDB;Data Source=myServer"

mySQL = "SELECT * from ...."
Set rstTmp = cnn1.Execute(mySQL)

Sheets("Sheet2").Activate
ActiveSheet.Cells.Select
Selection.Delete

With ActiveSheet.QueryTables.Add( _
Connection:=rstTmp, _
Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
' *****************************************************

Terence
 
Back
Top