How to print data in multiple excel sheets

narayanpvg Posts 1 Registration date Wednesday May 22, 2013 Status Member Last seen May 22, 2013 - May 22, 2013 at 01:03 AM
Dear All,

I am new to excel coding. I need help regarding the below issue

The below sub routine is the first sub routine. It gets the details from the excel forms a query

Private Sub CommandButton3_Click()
Dim dbserver_name1 As Variant
Dim dbserver_name2 As Variant
Dim dbserver_name As Variant
Dim schema_name As Variant
rownum = 2

For i = 2 To 65000
If Sheet1.Cells(i, 1).Value = "/*END*/" Then
Application.StatusBar = "Successfully Executed"
Exit Sub
End If
Application.StatusBar = "Processsin Row Number:" & Sheet1.Cells(i, 1) & " And " & Sheet1.Cells(i, 2)
dbserver_name = Sheet1.Cells(i, 3).Value
schema_name = Sheet1.Cells(i, 4).Value
domain_name = Sheet1.Cells(i, 1).Value
project_name = Sheet1.Cells(i, 2).Value

query = "****"

connection2 dbserver_name, schema_name, query, rownum
End Sub

The details "connection2 dbserver_name, schema_name, query, rownum" are fetched from sheet1 and passed on to the below sub routine

Public Sub connection2(dbserver As Variant, schemaname As Variant, query As Variant, rownum As Variant)

On Error GoTo errorsummary
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim fld As OraField
Dim currsheet As Worksheet
Dim newsheet As Workbook

Filename = "Data_" & Format(Date, "d-mmm-yy") & "_" & Format(Time, "hh-mm-ss")


Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase(dbserver, schemaname & "**", 0)
Debug.Print query
Set OraDynaset = OraDatabase.CreateDynaset(query, 0&)

fldcount = OraDynaset.Fields.Count

ReDim flds(0 To fldcount - 1)

For Colnum = 0 To fldcount - 1

Set flds(Colnum) = OraDynaset.Fields(Colnum)


For Colnum = 0 To OraDynaset.Fields.Count - 1

ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name


On Error Resume Next
For j = 2 To OraDynaset.RecordCount + 1

For Colnum = 0 To fldcount - 1

ActiveSheet.Cells(rownum, Colnum + 1) = flds(Colnum).Value


Debug.Print rownum & "," & RecordCount
rownum = rownum + 1

If Err.Number > 0 Then
Debug.Print Err.Description
End If

End Sub

The issue is when all rows of sheet2 are written with the data about 65000 rows then the data is not written to next sheets and the macro throws an error. What I want is the data should should be written to next sheets when first sheet is filled with data i.e once Sheet 2 is filled sheet 3 should be filled and so on...

Please help me to tweak the code

Thanks in Advance