Hi,
I am trying to pull data from Oracle using ADOconnection.
I was able to connect to Oracle, pull some data, and pasted into
worksheets, but every Japanese characters were garbled like "??????".
I appreciate if anyone can help.
I think my Excel uses "shift-jis" character set while Oracle data uses
UTF-8.
Thanks
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+
Sub Connect2Oracle()
' Define parameters/arguments
Dim mySQL As String
Dim oConOracle
Dim oRsOracle
Dim strConOracle As String
Dim colCounter As Long
' Define ADOConnection properties
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
'Connect to the data base - Refer to tnsname.ora
' XXXXXXX is taken from tnsname.ora
strConOracle = "Driver={Microsoft ODBC for Oracle}; XXXXXXXXX"
' Open Connection to Oracle
oConOracle.Open strConOracle
' Return connection message
If Err = 0 Then
MsgBox "Connection successful
Else
MsgBox "Connection failed"
End If
' SQL query
mySQL = "SELECT * FROM " & gPRD_AUD
' StatusBar
Application.StatusBar = Now & " Running query number: 1"
' Execute SQL
Set oRsOracle = oConOracle.Execute(mySQL)
' Header
For colCounter = 1 To oRsOracle.Fields.Count
With wksOutput
MsgBox oRsOracle.Fields(colCounter - 1).Name
.Cells(1, colCounter).Value = oRsOracle.Fields(colCounter -
1).Name
End With
Next
' Start copy
wksOutput.Range("A2").CopyFromRecordset oRsOracle
' Disconnect
oConOracle.Close
Set oConOracle = Nothing
End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
