Error trying to create multiple Excel worksheets per session

[charvi]

Can I generate more than 1 Excel sheet when a command button is clicked?
For eg
I search for 2 names of audit dept and click print command button where records will be printed in Excel.
Again if I search for other 2 names and if I click print command button reports will not be printed.and gives error I have to close the program and load the program again.
If yes can you tell me how?
When I write a code when it executes or second Excel sheet it gives error

[hariharanmca]

can i generate more than 1 excel sheets when a command button is clicked
for eg
i search for 2 names of audit dept and click print command button where records will be printed in excel
again if i search for other 2 names and if i click print command button reports will not be printed.and gives error i have to close the program and load the program again
if yes can u tell me how.
hen i wrte a cde whenit eecutes or second exce sheet it gives error

--------------------------------------------------------------------------------

Can you explain detail?
VB Version?
you mean Report or excel sheet, then there versions?

[charvi]

Can you explain detail?
VB Version?
you mean Report or excel sheet, then there versions?

I am using VB6. I have coded in VB6 to generate an Excel sheet and put the data from Access to Excel sheet.
First time data will be placed in Excel sheet appropriately.
But again when I want to create it gives error
Object variable or with block variable not set
please help.Very urgent.
Thanks

[Killer42]

I think we'll need to see the code. It sounds as though you have created an Excel object in your program at startup or something, then released it (Set ... = Nothing) after using it, so then you can't use it again.

[QVeen72]

Hi,

U will have to Release all the objects related to Excel, Otherwise u will get that error, Close and Set to Nothing xlApp object and xlwrkbook/xlsheet objects..

REgards
Veena

[charvi]

I am using VB6. I have coded in VB6 to generate an Excel sheet and put the data from Access to Excel sheet.
First time data will be placed in Excel sheet appropriately.
But again when I want to create it gives error
Object variable or with block variable not set
please help.Very urgent.
Thanks

[CODE=vb]Dim objexcel As excel.Applicati on
Set objexcel = New excel.Applicati on
objexcel.Visibl e = True
objexcel.Sheets InNewWorkbook = 4
objexcel.Workbo oks.Add


Dim rsa As New ADODB.Recordset
rsa.Open "select count(*) from temp", conn, adOpenDynamic, adLockOptimisti c
With rsa
Dim s As Integer
s = rsa.Fields(0)
Dim a As Integer
End With
rsa.Close

Dim rs As New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.Open "select * from temp", conn, adOpenDynamic, adLockOptimisti c

Dim strSQL As String
Dim n As Integer
Dim intcount2 As Integer
intcount2 = 0
intcount = 0
With rs
While rs.EOF = False
If rs.Fields(0) < 7 Then
ActiveSheet.Cel ls(intcount + 2, 1) = !Name
ActiveSheet.Cel ls(intcount + 3, 1) = !Des
ActiveSheet.Cel ls(intcount + 4, 1) = !comp[/CODE]
pls help this is th code

[QVeen72]

hI,

write this code at the end:

Set objexcel = Nothing

REgards
Veena

[charvi]

hI,

write this code at the end:

Set objexcel = Nothing

REgards
Veena

write objexcel=noting
but it dos not work

[QVeen72]

Hi,

If u already ebcountered that problem, u need to restart ur system and at the end it is "Set ObjExcel = Nothing"


REgards
Veena

[charvi]

Hi,

If u already ebcountered that problem, u need to restart ur system and at the end it is "Set ObjExcel = Nothing"


REgards
Veena

still it is not working

[Killer42]

...ActiveSheet. Cells(intcount + 2, 1) = !Name ...

Are you sure this is VB6? I wouldn't have thought VB would understand ActiveSheet. Isn't that a VBA thing?

I don't have time to check this at the moment, sorry.