classic asp delete rows and columns from an excel file

Question

how can I remove specific rows and columns from an excel file using only classic ASP? For example, given the excel file

col1  col2  col3
one   two   three
four  five  six

I want to be able to programmatically delete the first row and second column to produce

one   three
four  six

Thanks!

One more thing I should have mentioned -- the excel file are not necessarily in a nice tabular form (not necessarily headers followed by data), so for example there may be blank rows at the top of the file. As far as I can tell, this precludes using the Jet OLE DB provider. — rdm

dmb dmb · Accepted Answer · 2010-04-02T21:19:59

You can try using an Excel.Application object. For example:

dim oExcel, oWkBk  
set oExcel = CreateObject( "Excel.Application" )
oExcel.Visible = false
oExcel.DisplayAlerts = false
set oWkBk = oExcel.WorkBooks.Open( "C:\path\file.xls" )

You can then delete any individual cells with:

oExcel.Cells( 1, 1 ).Delete

Or entire rows/columns with:

oExcel.Cells(1,1).EntireColumn.Delete
oExcel.Cells(1,1).EntireRow.Delete

To check if a cell is empty use:

if isEmpty(oExcel.Cells(1,1)) then ...

Finally, cleanup:

oWkBk.Close()
oExcel.Quit()
set oWkBk = nothing
set oExcel = nothing

For more info, try Googling things like "excel application object vbscript." You can find many examples. Unfortunately, I've found it impossible to find a complete reference.

classic asp delete rows and columns from an excel file

1 Answers