Passing recordset from VB to Excel macros

[sanket_bakshi]

Hello,
I am trying to pass two recordsets from a VB application to a Excel Macro...
Though this works for the first time, it starts failing the second time on.
The recordsets passed are recieved in the macro with 0 records though while passing the recordsets do have a certain number of records...

I have a feeling that some Excel settings are being modified by the code and hence it fails the second time onwards. But I dont make any statements at this point. Please let me know what I am doing wrong !

Following is the code that I am using -

Please let me know if I am doing anything wrong !!!

Regards,
Sanket



option Explicit
Dim withevents xlApp as Excel.Application
Dim withevents xlBook as Excel.Workbook
Dim Sheet as Object
Dim printCancel as Boolean

public Qt_Header as Recordset
public Qt_Details as Recordset

public Function PrintQuotation()
Dim cnt as Integer
printCancel = false
set Qt_Header = parentChild.ParentComplete
set Qt_Details = parentChild.ChildComplete

cnt = 1
set xlBook = nothing
set xlBook = Workbooks.Open("C:\Quotation.xlt")
xlBook.Application.Run "Sheet1.ConnectD", Qt_Header, Qt_Details, objConn, 1
set xlApp = xlBook.Application
Do While cnt <= xlBook.Application.CommandBars("Worksheet menu Bar").Controls.Count
If xlBook.Application.CommandBars("Worksheet menu Bar").Controls(cnt).Caption <> "&File" then
xlBook.Application.CommandBars("Worksheet menu Bar").Controls(cnt).Visible = false
End If
cnt = cnt + 1
Loop

cnt = 1
Do While cnt <= xlBook.Application.CommandBars("Worksheet menu Bar").Controls("File").Controls.Count
on error GoTo StopDisable
If xlBook.Application.CommandBars("Worksheet menu Bar").Controls("File").Controls(cnt).Caption <> "&print..." then
If xlBook.Application.CommandBars("Worksheet menu Bar").Controls("File").Controls(cnt).Caption <> "E&xit" then
xlBook.Application.CommandBars("Worksheet menu Bar").Controls("File").Controls(cnt).Visible = false
End If
End If
cnt = cnt + 1
Loop
StopDisable:
xlBook.Application.Interactive = true
xlBook.Application.EnableEvents = true
xlBook.Application.DisplayFullScreen = true
xlBook.Application.CommandBars("Full Screen").Visible = false
xlBook.Application.CommandBars("Standard").Visible = false
xlBook.Application.Visible = true
xlBook.Windows(1).Visible = true
me.Visible = false
End Function

private Sub Form_Load()
PrintQuotation
End Sub

private Sub Form_Unload(Cancel as Integer)
xlBook.Close false
xlApp.Quit
set xlBook = nothing
End Sub

private Sub xlBook_BeforeClose(Cancel as Boolean)
restoreExcelEnvironment
Unload me
End Sub

private Sub xlBook_BeforePrint(Cancel as Boolean)
If printCancel = false then
printCancel = true
else
printCancel = false
End If
xlBook.SaveAs ("c:\sanket.xls")
set Sheet = xlApp.ActiveWorkbook.Sheets("Sheet1")
Sheet.PrintOut
Cancel = printCancel
If printCancel = false then
printCancel = true
else
printCancel = false
End If
End Sub

private Sub restoreExcelEnvironment()
xlBook.Application.CommandBars("Worksheet menu bar").Reset
xlBook.Application.DisplayFullScreen = false
xlBook.Application.CommandBars("Standard").Visible = true
End Sub