oracle 导出中文乱码

xuezsf 2019-05-15 05:04:57

VBA访问Oracle数据库，导出数据中的中文为乱码，如何解决？

Sub 销售订单()
Dim strConn As String
Dim dbconn As Object
Dim resSet As Object

Set dbconn = CreateObject("ADODB.Connection")
Set resSet = CreateObject("ADODB.Recordset")

strConn = "Driver={Oracle in Oracleinstantclient_12_2};user id=xxxxxxx ;password=xxxxxxxx ;data source=atiprod;Persist Security info=True"

dbconn.CommandTimeout = 380
dbconn.Open strConn
sqls = "select CZH.order_number,CZ.line_number,CZ.ordered_item,CZH.cust_po_number," & _
" CZ.customer_job FRE销售订单,FRE.cust_po_number 最终客户PO,CZ.ordered_quantity," & _
" hz_parties.party_name," & _
" b_use.address1||' '||b_use.address2||' '||b_use.address3||' '||b_use.address4||' '||b_use.city||' '||b_use.postal_code||' '||b_use.state||' '||b_use.province||' '||b_use.country 发票地址," & _
" s_use.address1||' '||s_use.address2||' '||s_use.address3||' '||s_use.address4||' '||s_use.city||' '||s_use.postal_code||' '||s_use.state||' '||s_use.province||' '||s_use.country 发货地址," & _
" CZ.order_quantity_uom,CZ.unit_selling_price,CZH.transactional_curr_code,CZ.request_date,CZ.promise_date," & _
" CZ.schedule_ship_date,CZ.ship_to_org_id,CZ.invoice_to_org_id,CZ.user_item_description,CZ.booked_flag" & _
" from oe_order_lines_all CZ,oe_order_headers_all CZH,oe_order_headers_all FRE,hz_parties,hz_cust_site_uses_all ship_use," & _
" hz_cust_site_uses_all bill_use,hz_cust_accounts,hz_cust_acct_sites_all ssite,hz_cust_acct_sites_all bsite," & _
" hz_party_sites shps,hz_party_sites bhps,hz_locations s_use,hz_locations b_use" & _
" where hz_parties.party_id = hz_cust_accounts.party_id and hz_cust_accounts.cust_account_id = ssite.cust_account_id" & _
" and hz_cust_accounts.cust_account_id = bsite.cust_account_id AND ssite.party_site_id = shps.party_site_id" & _
" AND bsite.party_site_id = bhps.party_site_id and s_use.location_id = shps.location_id and b_use.location_id = bhps.location_id" & _
" AND ship_use.cust_acct_site_id = ssite.cust_acct_site_id AND bill_use.cust_acct_site_id = bsite.cust_acct_site_id" & _
" and CZ.org_id=2251 and FRE.order_number(+) = CZ.customer_job and CZH.sold_to_org_id = hz_cust_accounts.cust_account_id" & _
" and CZ.ship_to_org_id = ship_use.site_use_id and CZ.invoice_to_org_id = bill_use.site_use_id and CZH.header_id = CZ.header_id" & _
" and CZ.flow_status_code not in ('CLOSED','CANCELLED') and CZH.order_number='515007007' and CZ.line_type_id not in ('1966')" & _
" and CZ.invoice_to_org_id not in ('455804') order by CZ.schedule_ship_date,CZH.order_number,CZ.line_number;"
Set x = dbconn.Execute(sqls)
Sheet1.Range("a1").CopyFromRecordset x

End Sub

...全文