Export to Excel using ExcelBuffer

zeon

Hi,

I have an existing Excel file containing five worksheets. I need to export data from Navision to one of those worksheets.

The sheet already contains data, so I need a way to clear the sheet before I make my export. Is there a way to do that or delete an entire sheet, or do I have to run through all columns and rows with data and delete their contents? I'm using Excelbuffer to export the data.

I have tried to just update the sheet with new data, but the problem is that in one situation the export is 500 rows, and the data contains data in 1000 rows, so the last 500 rows is not deleted.

/zeon

cnicola

I would think the easiest way is to delete the Sheet and then recreate it.
I looked at the Excel Automation and cannot see a clear all.
You probably could do it by defining a range variable that includes the whole sheet and then clear that way but pretty convoluted.

zeon

Ok, if I use the Excel Automation and then delete the sheet the user is prompted with the message that the sheet possibly contains data and if the user wants to delete the data.
Can I avoid that message, so that no user interaction is needed?

Another question - is it possible to get the row number returned of the last row which contains data?

/zeon

zeon

I solved the problem. In stead of deleting the sheet and the create a new one, it is easier to just clear the sheet before export.

xlRange := xlWorksheet.Range('A1:G65536');
// 65536 is max. no of cells in Excel (2003). I only needed to clear in the range from A->G-cell.
xlRange.Clear;

/zeon