VB Forum / COM / November 2004

Hi Robert,

1. The most efective manner of populating a database is through recordsets
only. Look at this article for a complete code snippet. You can cross-check
to see if anything is missing at your end :

[How To Use the Spreadsheet Web Component with Visual Basic]
http://support.microsoft.com/kb/235883/EN-US/

Most likely, it is slow because of the large number of records. You can try
breaking up your tables into smaller ones through normalization, and hence a
better planned database.

2. I am not sure if I got the second part. Even if the user has closed the
spreadsheet window, you would still need to clear the instances of the Excel
object you created from the memory. Use this code to clear it :
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

Make sure you run this code while quitting the app, to avoid overheads.

HTH
Anushi

¤ I've got a VB 6 project where  I am populating a spreadsheet with data from
¤ an ADO recordset and I've got a couple of questions.
¤
¤ First, is anyone aware of a way to populate the spreadsheet quickly?  Right
¤ now, I'm cycling through every record and field of the recordset, reading
¤ the value from the recoordset, and then pasting the value into the the
¤ corresponding spreadsheet cell.  Seems to be very slow!

Have you tried the CopyFromRecordset method?

¤
¤ Second, is there any way to check to see if the user has manually closed the
¤ Excel session before I attempt to close it in code?

You could try the GetObject function.

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)