CreateObject("Excel.Application") does not work

Question

As a background, I am trying to delete the 2nd row of my 2007 Excel work book. I dont have MS Office installed on the server and installing them is not a possibility. Using interop Assemblies is of no use since I cant install office. When I tried using the VB script (below) to manipulate the file, it gives me all sorts of errors:

Dim filename As String
Dim appExcel As Object
Dim newBook As Object
Dim oSheet1 As Object

appExcel = CreateObject("Excel.Application")
filename = "C:\test.xls"
appExcel.DisplayAlerts = False

newBook = appExcel.Workbooks.Open(filename)
oSheet1 = newBook.worksheets("Sheet1")
oSheet1.Range("A2").Entirerow.Delete()


newBook.SaveAs(filename, FileFormat:=56)
appExcel.Workbooks.Close()
appExcel.Quit()

I kinda get it that without excel and assemblies, I cant manipulate the file. Is there any other way to achieve this? I dont know what assemblies I can try using. Thanks for reading.

Depending on what libraries you do have installed - you can create an ADODB connection and query the workbook as if it were a database instead. There are plenty of resources on how to do this but ultimately it's hard to give an answer because we don't know your server. — SierraOscar
What libraries are we looking for? I can search the server to find if I have the appropriate library for ADODB. — rvphx
it should be called adodb.dll - it's a .NET library so it should be in your Microsoft.NET directory in program files. — SierraOscar
Just to save you a wasted effort though - I just re-read your question and realised you're trying to delete rows, you can't do this with ADO unfortunately. — SierraOscar
ah ok! Gotcha. I was exploring this route as an option. My final option would only be to install EPPlus library from Codeplex. — rvphx

SierraOscar SierraOscar · Accepted Answer · 2016-05-10T19:22:17

No, without the required Excel Objects library you can't successfully delete rows from an Excel file* via a scripting language in this way.

You can query the workbook using ADO and SQL but this won't allow you to delete rows.

*by Excel file, I mean an .xls file which is what you appear to be working with - .xlsx files (Excel 2007 and newer) are actually just compressed XML files and can be edited as such if you have the know-how

CreateObject("Excel.Application") does not work

1 Answers