1748181 Members
3706 Online
108759 Solutions
New Discussion юеВ

connecting to oracle

 
Prabhu_7
Frequent Advisor

connecting to oracle

Can anyone help me connecting to oracle from excel using macro.Does any one has a sample Excel file for this? Exact reqmnt is, as soon as we open the excel file it should connect to database and retreive data.Please help. If any one has sample excel file which does this job, please send it to me.Very urgent

Thanks
2 REPLIES 2
John Flanagan
Regular Advisor

Re: connecting to oracle

Dim RecordCount
' Create and initialize the necessary objects
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim EmpDynaset As OraDynaset
Dim ColNames As OraFields
Dim Record
Dim SheetDeliverySchedule As Worksheet
Dim SheetPickingList As Worksheet
Dim QueryLib As VisQuery
Dim flds() As Object
Dim fldcount As Integer
Dim BackorderRecordCount As Integer


Sub Get_DeliverySchedule()

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("SID", "user/password", 0&)

Set SheetDeliverySchedule = Worksheets.Add
SheetDeliverySchedule.Select
SheetDeliverySchedule.Name = "DeliverySchedule"

Set QueryLib = New VisQuery
Set EmpDynaset = OraDatabase.CreateDynaset(QueryLib.DeliveryScheduleWhse10, 0&)

fldcount = EmpDynaset.Fields.Count
ReDim flds(0 To fldcount - 1)
For Colnum = 0 To fldcount - 1
Set flds(Colnum) = EmpDynaset.Fields(Colnum)
Next


'Insert Column Headings
For Colnum = 0 To EmpDynaset.Fields.Count - 1
ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name
Next

'Display Data
For Rownum = 2 To EmpDynaset.RecordCount + 1
For Colnum = 0 To fldcount - 1
ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
Next
EmpDynaset.DbMoveNext
Next

BackorderRecordCount = ActiveCell.SpecialCells(xlLastCell).Row
End Sub
malay boy
Trusted Contributor

Re: connecting to oracle

well you need to install Oracle Objects for OLE.

I attached here the sample.

regards
mB
There are three person in my team-Me ,myself and I.