大师帮忙啊,EXCEL直接从后台ORACLE数据库取数生成会计报表!

adders

hxj710802 发表于 2012-1-14 07:04
请各位大师百忙中写段代码VBA+ADO，不胜感激

我没用过Oracle,不过工作中会常用到Excel VBA连接到Access数据库,想来连接Oracle, dB2, MySQL, SQL Server等数据库的原理大致应该是相通的.你试试以下结构(其中MySQL语句请自行试验完成，顺便说一句，#对于日期的处理只适用于Access,而Oracle是以Date 'yyyy-mm-dd'来定义日期的; 如果有需要设定变量的部分,VBA可较容易实现,如介于某两日期之间,或"科目编码"=某单元格内容,等等)

Sub Ora_query()
Dim MyWS as Worksheet
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

Set MyWS = ThisWorkbook.ActiveSheet
MyConnect = "rovider=MSDAORA.1assword=xxxxx;User ID=GADATA0011;Data Source=xxxxxersist Security Info=True"
MySQL = "SELECT a.STRACCOUNTCODE, a.STRACCOUNTNAME, ... " & _
             "FROM ACCOUNT a, AccountDaily b, Currencys c, Customer d, .... " & _
             "WHERE .... " & _
             "Group By a.STRACCOUNTCODE, a.STRACCOUNTNAME, a.INTDIRECTION " & _
             "Order By a.STRACCOUNTCODE, a.STRACCOUNTNAME, a.INTDIRECTION; "

Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnect                  

MyWS.Activate
Cells.ClearContents
Range("A6").CopyFromRecordset MyRecordset
Range("A5:I5").Value = array("科目编码", "科目名称", "期初余额本币金额", "本期借方发生额本币金额", "本期贷方发生额本币金额", "期末余额本币金额", .....)

......

Set MyWS = Nothing
Set MyRecordset = Nothing
End Sub

hxj710802

谢11楼,我试下,再来回报

hxj710802

Sub Ora_query()
Dim MyWS As Worksheet
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String
Set MyWS = ThisWorkbook.ActiveSheet
MyConnect = "rovider=MSDAORA.1assword=gold;User ID=GADATA0011;Data Source=oradb; Persist Security Info=True"
MySQL = "select ACCOUNT.STRACCOUNTCODE As '科目编码'," & _
          "       ACCOUNT.STRACCOUNTNAME As '科目名称'," & _
          "      SUM(DECODE(ACCOUNT.INTDIRECTION,1,1,0)" & _
          "             *DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
          "         -TO_DATE('2011-12-01','YYYY-MM-DD')),-1,1,0)" & _
          "             *((dblUnPostedDebit+dblPostedDebit)" & _
          "         -(dblUnPostedCredit+dblPostedCredit)))" & _
          "      +SUM(DECODE(ACCOUNT.INTDIRECTION,-1,1,0)" & _
          "              *DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
          "          -TO_DATE('2011-12-01','YYYY-MM-DD')),-1,1,0)" & _
          "              *((dblUnPostedCredit+dblPostedCredit)" & _
          "          -(dblUnPostedDebit+dblPostedDebit))) " & _
          "As '期初余额本币金额',"
   MySQL1 = "SUM(DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD') " & _
         "   - TO_DATE('2011-12-01','YYYY-MM-DD')),-1,0,1)" & _
         "         *DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
         "   - TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)*(dblUnPostedDebit+dblPostedDebit)) " & _
         " As '本期借方发生额本币金额'," & _
         " SUM(DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
         "   - TO_DATE('2011-12-01','YYYY-MM-DD')),-1,0,1)" & _
         "         *DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
         "   - TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)" & _
         "         *(dblUnPostedCredit+dblPostedCredit)) " & _
         " As '本期贷方发生额本币金额',  "
         
     MySQL2 = " DECODE(ACCOUNT.INTDIRECTION,1,'借','贷') As '方向', " & _
           " SUM(DECODE(ACCOUNT.INTDIRECTION,1,1,0)" & _
           "            *DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
           "     -TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)" & _
           "            *((dblUnPostedDebit+dblPostedDebit)" & _
           "     -(dblUnPostedCredit+dblPostedCredit)))" & _
           "+SUM(DECODE(ACCOUNT.INTDIRECTION,-1,1,0)" & _
           "               *DECODE(SIGN( TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')" & _
           "           -TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)" & _
           "               *((dblUnPostedCredit+dblPostedCredit)" & _
           "           -(dblUnPostedDebit+dblPostedDebit))) " & _
           "As '期末余额本币金额'," & _
           "max(Account.blnIsDetail)," & _
           "max(Account.intLevel)," & _
           "Max(Account.lngAccountTypeID)  "
           
          '两个Organization,Organization   OrganizationAux "!
    MySQL3 = "From Account,     AccountDaily,  Currencys," & _
          "     Customer,    CustomerType,  Employee, " & _
          "     Organization,Organization   OrganizationAux  "
         
    MySQL4 = "Where ((Upper(Account.strAccountCode)=Upper('521') Or Account.strAccountCode Like '521-%'))" & _
          "    And Account.lngAccountID=AccountDaily.lngAccountID" & _
          "    And Currencys.lngCurrencyID(+)=AccountDaily.lngCurrencyID" & _
          "    And Customer.lngCustomerID(+)=AccountDaily.lngCustomerID " & _
          "    And Customer.lngCustomerTypeID = CustomerType.lngCustomerTypeID(+)" & _
          "    And Employee.lngEmployeeID(+)=AccountDaily.lngEmployeeID " & _
          "    AND AccountDaily.lngOrganizationID = Organization.lngOrganizationID(+) " & _
          "    AND AccountDaily.lngSourceOrganizationID = OrganizationAux.lngOrganizationID(+)" & _
          "    And Abs(dblPostedDebit)+ Abs(dblUnPostedDebit)+Abs(dblPostedCredit)" & _
          "            +Abs(dblUnPostedCredit)+ Abs(dblCurrencyPostedDebit)+Abs(dblCurrencyUnPostedDebit)" & _
          "            +Abs(dblCurrencyPostedCredit)+Abs(dblCurrencyUnPostedCredit)" & _
          "            +Abs(dblQuantityPostedDebit)+Abs(dblQuantityUnPostedDebit)" & _
          "            +Abs(dblQuantityPostedCredit)+Abs(dblQuantityUnPostedCredit)<>0 " & _
          "    AND AccountDaily.strDate <= '2011-12-31' " & _
          "    Group By ACCOUNT.STRACCOUNTCODE,ACCOUNT.STRACCOUNTNAME,ACCOUNT.INTDIRECTION " & _
          "    Order By ACCOUNT.STRACCOUNTCODE,ACCOUNT.STRACCOUNTNAME,ACCOUNT.INTDIRECTION "
    MySQL = MySQL & MySQL1 & MySQL2 & MySQL3 & MySQL4
   
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnect
MyWS.Activate
MyWS.Cells.ClearContents
Range("A6").CopyFromRecordset MyRecordset
Range("A5:I5").Value = Array("科目编码", "科目名称", "期初余额本币金额", "本期借方发生额本币金额", "本期贷方发生额本币金额", "期末余额本币金额")
Set MyWS = Nothing
Set MyRecordset = Nothing
End Sub

hxj710802

提示: 运行时错误

    自动化错误

adders

hxj710802 发表于 2012-1-19 03:24
提示: 运行时错误

    自动化错误

VBA连数据库最容易出错的就是SQL部分,所以如果高亮显示在以下这一行,你需要好好整理一下SQL,包括标点符号和空格都很重要,最好能精简再精简,实在不行,可分段测试:
MyRecordset.Open MySQL, MyConnect

精简SQL时首先建议用Table Alias,比如Account这个表就可以代号为a, AccountDaily代号为b,等等,参考我前贴的例子:
SELECT a.STRACCOUNTCODE, a.STRACCOUNTNAME
FROM ACCOUNT a, AccountDaily b, Currencys c, Customer d...

另外象你的MYSQL3语句中,Organization   OrganizationAux是什么?中间的空格肯定会导致出错,如果这张表的名字叫做Organization   OrganizationAux那么你应该用中括号[Organization   OrganizationAux]表示(或者参考Oracle有关说明),更可能的情况,是不是你多写了前面这个Organization,或者你遗漏了中间的下划线(如Organization_OrganizationAux)?

还有最后结束整个SQL时是不是需要分号?在连接Access时是可有可无这个分号的,但Oracle是不是一定要以分号结束SQL语句?

作为不能连接你数据库测试你SQL的外人,很难给你直接的答案,只能提供一个思路