How to print data in multiple excel sheets [Closed]

Report
Posts
1
Registration date
Wednesday May 22, 2013
Status
Member
Last seen
May 22, 2013
-
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
Next
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")

Sheet2.Activate

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)

Next


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

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

Next


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

Next

OraDynaset.MoveNext
Debug.Print rownum & "," & RecordCount
rownum = rownum + 1
Next

errorsummary:
If Err.Number > 0 Then
Debug.Print Err.Description
End If
OraDatabase.Close

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

Narayan

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!