We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.
Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?
I don't know of a way to do without a bit of scripting.
You could output all rows to a csv, then run vbscript that uses the excel object to create a new workbook and create multiple sheets (tabs - this is what you mean, right?), iterating through the csv.
Will people actually be looking at the data, row after row in excel? Why not use an access database? Excel wasn't designed for such large amounts of data, so using it for such a thing might not be a good idea in the first place.