469,609 Members | 1,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Error trying to create multiple Excel worksheets per session

121 100+
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
Aug 27 '07 #1
10 1621
hariharanmca
1,977 1GB
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?
Aug 27 '07 #2
charvi
121 100+
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
Aug 27 '07 #3
Killer42
8,435 Expert 8TB
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.
Aug 27 '07 #4
QVeen72
1,445 Expert 1GB
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
Aug 27 '07 #5
charvi
121 100+
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

Expand|Select|Wrap|Line Numbers
  1. Dim objexcel As excel.Application
  2. Set objexcel = New excel.Application
  3. objexcel.Visible = True
  4. objexcel.SheetsInNewWorkbook = 4
  5. objexcel.Workbooks.Add
  6.  
  7.  
  8. Dim rsa As New ADODB.Recordset
  9. rsa.Open "select count(*) from temp", conn, adOpenDynamic, adLockOptimistic
  10. With rsa
  11.   Dim s As Integer
  12.   s = rsa.Fields(0)
  13.   Dim a As Integer
  14. End With
  15. rsa.Close
  16.  
  17. Dim rs As New ADODB.Recordset
  18. rs.CursorLocation = adUseClient
  19. rs.Open "select * from temp", conn, adOpenDynamic, adLockOptimistic
  20.  
  21. Dim strSQL As String
  22. Dim n As Integer
  23. Dim intcount2 As Integer
  24. intcount2 = 0
  25. intcount = 0
  26. With rs
  27.     While rs.EOF = False
  28.         If rs.Fields(0) < 7 Then
  29.             ActiveSheet.Cells(intcount + 2, 1) = !Name
  30.             ActiveSheet.Cells(intcount + 3, 1) = !Des
  31.             ActiveSheet.Cells(intcount + 4, 1) = !comp
pls help this is th code
Aug 27 '07 #6
QVeen72
1,445 Expert 1GB
hI,

write this code at the end:

Set objexcel = Nothing

REgards
Veena
Aug 27 '07 #7
charvi
121 100+
hI,

write this code at the end:

Set objexcel = Nothing

REgards
Veena
write objexcel=noting
but it dos not work
Aug 27 '07 #8
QVeen72
1,445 Expert 1GB
Hi,

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


REgards
Veena
Aug 27 '07 #9
charvi
121 100+
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
Aug 27 '07 #10
Killer42
8,435 Expert 8TB
...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.
Aug 28 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.