filling sheets with Oracle ????
 

Hi,

I'm trying to write a procedure that will send 65000 records by
sheet. I'm creating the sheet dynamically. Programming in excel is new and
I'm still not sure how to call the objects of the workbook. I'm not sure if
sh is at the right place and if I can switch the sheet number with a
variable.....Can you help me?

Public Sub odbc()
Dim rng As range
Dim sh As Worksheet
Set sh = ActiveSheet

Dim SheetNumber
Dim NumberOfRecord
Dim Partition
'total nuber of record
NumberOfRecord = oraDynaSet.RecordCount
'number of sheets that will have 65000 and the last one with less
Partition = NumberOfRecord / 65000

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("", "Userid/Pwd", 0)

Sql = "select * from Table"

Set oraDynaSet = objDatabase.DBCreateDynaset(Sql, 0)
'First sheet to fill out
If oraDynaSet.RecordCount 0 And oraDynaSet.RecordCount <= 65000 Then
oraDynaSet.MoveFirst
'Header
For x = 0 To oraDynaSet.Fields.Count - 1
sh.Cells(1, x + 1) = oraDynaSet.Fields(x).Name
sh.Cells(1, x + 1).Format = Bold
Next
'Records
For Y = 0 To oraDynaSet.RecordCount - 1
For x = 0 To oraDynaSet.Fields.Count - 1
sh.Cells(Y + 2, x + 1) = oraDynaSet.Fields(x).Value
Next
oraDynaSet.MoveNext
Next
End If

'the first partition is done
Partition = Partition - 1

'other sheets

For SheetNumber = 1 To Partition
'creating a new sheet by calling a procedure
Createsheet ("sheet#" & SheetNumber)
'Going in the intervals
'65001 - 130 000 in sheet#1
'130001 - 195000 in sheet#2
'195001 - 260000 in sheet#3
' .... up to Partition

sh.Select (SheetNumber) ????????????????????????? how can I go to
another sheet from VB?

If oraDynaSet.RecordCount ((SheetNumber * 65000) + 1) And_
oraDynaSet.RecordCount <= ((SheetNumber + 1) * 65000) Then
For Y = 0 To oraDynaSet.RecordCount - 1
For x = 0 To oraDynaSet.Fields.Count - 1
sh.Cells(Y + 2, x + 1) = oraDynaSet.Fields(x).Value
Next
oraDynaSet.MoveNext
Next
End If
Next


Set objSession = Nothing
Set objDatabase = Nothing
End Sub
thanx
Jack