I am new to VBA with Excel. Have b

[GSMike]

I am new to VBA with Excel.
Have been programming MS Access with VBA for some time.
I am wondering, does anyone have any experience with using VBA and Excel to do server-side processing with Excel, and what has your experience been. Our company uses SQL Server, SAS, MS Access, SYBASE, DBII, etc.
I just recently downloaded the ODBC driver for SAS.
Actually, I have never done any server-side processing at all. All of my Access experience has been with the standard Access set-up (maybe an occasional linked table here or there).
Excel is particularly suitable to our environment, because of the wide variety of data formats we receive and the extremely short shelf-life of the datasets. Consequently, the goal here has become one of "receive and report". No time or patience on the part of users for loading data received in Excel into Access, SAS, etc.
If then, our desktop reporting needs are accomplished through Excel VBA, the next question would naturally be whether it would be feasible to do some server-side processing (for more serious, standardized data) through Excel as well. Is it possible to pass some SQL statements to SQL Server, for example?
Any insight at all is greatly appreciated.
Thanking you in advance... Mike Kemp
michael.kemp@gs.com

 

[ide]

I've created an Oracle DNS (MyDNSName), run query to Ora Applications (WIP, INV module) and copy recordset onto the active Excel book/sheet.

Sub main()
Dim sqlStr As String
Dim TFromField, TToField As String
Dim TAllCode As Integer
TFromField = "02-OCT-01": TToField = "02-OCT-01": TAllCode = 48

sqlStr = "SELECT SUBSTR(INV.MTL_SYSTEM_ITEMS.SEGMENT1, 5, 2) AlloyCode, INV.MTL_SYSTEM_ITEMS.SEGMENT1 Assembly, DATE_COMPLETED ComplDate, " & _
"SUBSTR(WIP_ENTITY_NAME,1,7) ChargeNo, SUM(QUANTITY_COMPLETED) SumComplQty From WIP_ENTITIES, " & _
"WIP_DISCRETE_JOBS, INV.MTL_SYSTEM_ITEMS " & _
"Where INV.MTL_SYSTEM_ITEMS.ORGANIZATION_ID = 227 " & _
"AND INV.MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID = WIP_DISCRETE_JOBS.PRIMARY_ITEM_ID AND WIP_DISCRETE_JOBS.ORGANIZATION_ID = 227 " & _
"AND WIP_ENTITIES.WIP_ENTITY_ID = WIP_DISCRETE_JOBS.WIP_ENTITY_ID AND WIP_ENTITY_NAME LIKE '01%' " & _
"AND (DATE_COMPLETED BETWEEN '" & TFromField & "' And '" & TToField & "') " & _
"AND SUBSTR(INV.MTL_SYSTEM_ITEMS.SEGMENT1, 5, 2) = '" & TAllCode & "' " & _
"Group By SUBSTR(INV.MTL_SYSTEM_ITEMS.SEGMENT1, 5, 2), " & _
"INV.MTL_SYSTEM_ITEMS.SEGMENT1, DATE_COMPLETED, SUBSTR(WIP_ENTITY_NAME,1,7) " & _
"Order By ComplDate, ChargeNo, AlloyCode"
Call CreateORASession(sqlStr)

End Sub

Public Sub CreateORASession(sqlStr As String)
Dim OraSession, OraDatabase, OraRecSet As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession&quot
Set OraDatabase = OraSession.OpenDatabase("MyDNSName", "MyUserName/MyPassword", 0&)

Set OraRecSet = OraDatabase.DbCreateDynaset(sqlStr, 0&)
OraRecSet.copytoclipboard -1
ActiveSheet.Paste
End Sub

i hope it helps you
ide