Generate Excel sheet through coding in VB?

[hdave]

Hello,

In my application i am generating execel sheet through coding. But it makes my application very slow.

is there any other more efficient and optimized way to do this.?

is it possible to run pre-defined macro in VB at runtime which accept data and generate excel sheet?

All inputs are welcomed.

Thanks in advance.

HDave

[RobDog888]

This should probably go in the VBA forum, but you may save time
by creating a template formatted as you want. Then you only
have to populate it with the data.

[hdave]

Hey,

Templates means one procedure in VB which generate excel sheet in certain format without data?

In my case no of data items is changing. So , templates of excel sheet has to chage according to no. of data items.

I will also try in VBA Section.

HDave

[si_the_geek]

By templates I think that RobDog888 means a pre-made Excel sheet without data, but with all other formatting. Then in your code yuo can simply copy the file and open it rather than making a new workbook.

This is a good idea, as it is actually the slowest part of the process (assuming the rest of the code is efficient!). For my programs formatting is generally around 50-70% of the creation time, but I cannot use templates because of various deployment issues.

is it possible to run pre-defined macro in VB at runtime which accept data and generate excel sheet?

I'm pretty sure you can, but it isn't noticably different in terms of speed.



SO, the importan question: how are you filling the sheet? Are you using something like Sheet.Cells(x,y) = "my text" ? If so, the chances are you can make the code take less than a third of the time it does at the moment (and maybe much less).

The .Cells() method is extremly slow, as it forces Excel to do its recalculating/reformating after every cell, rather than after a group of them.

If you are getting the data from a recordset, use the .CopyFromRecordset method that Excel provides - nothiing else you can do is faster.

If the data isn't from a recordset, you can fill the sheet using an array instead, eg:

Dim MyArray(2,3)
 
MyArray(1,1) = "test"
MyArray(1,2) = "this"
MyArray(1,3) = "speedy"
MyArray(2,1) = "little"
MyArray(2,2) = "beauty"
MyArray(2,1) = "!!!!"
 
'change xlSheet to the name of your sheet object
xlSheet.Range("A2:B4") = MyArray

(nb: this is from memory, there may be minor bugs!)

oh, and as you have variable amounts of data you will probably want to set different ranges (eg: A2:E54 and A2:F101). to set different ranges search for I funtion I made called xlcol or xl_col on this forum

[hdave]

Thanks!!! {RESOLVED}

I got it now.

[Dave Sell]

Originally posted by hdave
Hello,

In my application i am generating execel sheet through coding. But it makes my application very slow.

is there any other more efficient and optimized way to do this.?

is it possible to run pre-defined macro in VB at runtime which accept data and generate excel sheet?

All inputs are welcomed.

Thanks in advance.

HDave

I ran into this problem creating Excel spreadsheets via VB6 also.

I found that certain operations (dereferencing Range objects, inserting PageBreaks, and any general visual formatting) take extremely long and can drag your running time into the mud if placed hap-hazzardly in your loops.

Things that are fast are "data entry", where you are throwing data into cells, referenced directly from A1 on the worksheet.

For example, I made a reporting program that generated about a thousand pages in Excel. It took 40 hours to complete.

I dug around and made the above optimizations and got it down to about a half-hour.

If you are willing to spend some quality time with your code, you will find you can significantly reduce your running time.