large sql in VB to fill Excel cells ?
Date: 7 Mar 2002 01:06:41 -0800
Message-ID: <1ecec3ce.0203070106.727d5ccb_at_posting.google.com>
I have a large sql statement to call from Excel 97. I use Visual Basic (newbie).
When I have a small sql it is ok (the cells are filling ok), but I don't know how to call a large sql. Can I call directly a sql file ? it would be easier. Or a package ? but how to retrieve info in cells ???
Here the code I'm trying :
Sub connect()
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Dim ColNames As Object
'my sqlStmt is defined here on several lines of 255 chars max, and
separated with ' _' at the end (not the last line)
sqlStmt = "SELECT RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME,
(raa.address1||decode(raa.address1,null,null,',')||
raa.postal_code||decode(raa.postal_code,null,null,',')||
raa.city||decode(raa.city,null,null,',')|| raa.country),
ct.TRX_NUMBER, ct.TRX_DATE," _
cl.SALES_ORDER, cl.SALES_ORDER_DATE, SUM(cl.REVENUE_AMOUNT) "Somme
Bulletin" FROM RA_CUSTOMER_TRX_ALL ct, RA_CUSTOMERS RAC,
RA_CUSTOMER_TRX_LINES_ALL cl, RA_ADDRESSES_ALL RAA, RA_SALESREPS_ALL
RAS WHERE cl.REVENUE_AMOUNT <>0 AND CL.revenue_amount is" _
not null AND cl.SALES_ORDER IS NOT NULL AND ct.COMPLETE_FLAG ='Y' AND
ct.CUSTOMER_TRX_ID = cl.CUSTOMER_TRX_ID and exists (select 1 from RA_CUST_TRX_LINE_GL_DIST_ALL gl where cl.customer_trx_line_id = gl.customer_trx_line_id and GL.GL_DATE BETWEEN" _TO_DATE('01/02/2002', 'DD/MM/YYYY') AND TO_DATE('28/02/2002',
'DD/MM/YYYY')) AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+) AND
CT.ORG_ID = RAS.ORG_ID(+) AND ct.BILL_TO_CUSTOMER_ID = RAC.CUSTOMER_ID AND RAC.CUSTOMER_ID = RAA.CUSTOMER_ID GROUP BY" _ RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME, raa.address1, raa.postal_code, raa.city, raa.country, CT.TRX_NUMBER, ct.TRX_DATE, cl.SALES_ORDER, cl.SALES_ORDER_DATE ORDER BY RAS.NAME ASC, RAC.CUSTOMER_NUMBER ASC, RAC.CUSTOMER_NAME ASC, raa.address1," _ raa.postal_code, raa.city, raa.country, ct.TRX_NUMBER ASC, ct.TRX_DATE ASC, cl.SALES_ORDER ASC, cl.SALES_ORDER_DATE ASC"
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("ora11idb", "user/pass",
0&)
Set OraDynaset = OraDatabase.DbCreateDynaset(sqlStmt, 0&)
' Using field array, ie. ColNames("ename").value, is significantly
faster than using field lookup, ie. EmpDynaset.fields("ename").value
Set ColNames = OraDynaset.Fields
' Place column headings on sheet
' note : Excel is installed in French language
For icols = 1 To ColNames.Count
'For icols = 1 To 2
Worksheets("Feuil1").Cells(1, icols).Value = ColNames(icols - 1).Name
Next
' Check if the current dynaset row is valid
If OraDynaset.BOF <> True And OraDynaset.EOF <> True Then
' Place data on sheet using CopyToClipboard
OraDynaset.CopyToClipboard -1
Worksheets("Feuil1").Visible = True
Sheets("Feuil1").Select
Range("A2").Select
Sheets("Feuil1").Paste
End If
End Sub
When I try this I get 'Compile error / Syntax error' at 'sqlStmt' What is wrong ???
Thanks, Antoine
my e-mail please : antoine.theytaz at gmvs.migros.ch
Received on Thu Mar 07 2002 - 10:06:41 CET