Visual Basic application processing an Excel file

Question

I have a visual basic application where I open an excel file and process the data in the file. I import Microsoft Office Interop at the beginning of my code to allow me to access the excel file. My application runs properly on my local development machine (which has excel installed on it) but gets a dialog error when I deploy it to my companies server (where it will reside and be launched every day by a scheduled task).

This is the error that I get when I try to run the application on the server:

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80040154): Retrieving the 
COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} 
failed due to the following error: 80040154 Class not registered 
(Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
  at 
System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean 
publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& 
ctor, Boolean& bNeedSecurityCheck)at 
System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, 
Boolean fillCache, StackCrawlMark& stackMark)
at 
System.Activator.CreateInstance(Type type, Boolean nonPublic)
at System.Activator.CreateInstance(Type type)
at VPA_password_database_update.Form1.convertCredFile(String testOrLive)
at VPA_password_database_update.Form1.Form1_Load(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I installed my application on a co-workers machine (which has excel installed on it) and the application runs fine.

I believe excel is not installed on my companies server (where the application needs to reside).

I was under the impression that with the import of the Microsoft Office Interop, I would not need to have Excel installed on the machine running the application.

Is that impression incorrect?

Thanks.

Answer 1

'vb .net code

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim dtreadExcel As DataTable
        dtreadExcel = New DataTable
        dtreadExcel = Read_Excel("D:\26July2022-Task\Copy_of_Book17__2_.xlsx", "Sheet1")
    End Sub



    Public Function Read_Excel(ByVal fileName As String, ByVal sheetName As String, Optional ByVal Filter As String = "") As DataTable
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dataResult As New DataTable
        Dim sheetName1 As String

        Try
            conn = New System.Data.OleDb.OleDbConnection(openConn_String_XL(fileName))
            conn.Open()
            Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
            Dim dr As DataRow = dt.Rows(0)
            sheetName1 = dr(2).ToString()
            Dim command As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" + sheetName + "$]")
            If sheetName = "" Then
                command = New System.Data.OleDb.OleDbCommand("SELECT * FROM [" + sheetName1 + "]")
            End If

            command.Connection = conn
            Dim adaperForExcelBook As New System.Data.OleDb.OleDbDataAdapter
            adaperForExcelBook.SelectCommand = command
            adaperForExcelBook.Fill(dataResult)

            Read_Excel = dataResult.Copy
            conn.Close()

        Catch ex As Exception

        Finally

            'ObjectDispose(adaperForExcelBook)

            ObjectDispose(dataResult)

            ObjectDispose(conn)

        End Try

    End Function
    Public Sub ObjectDispose(ByRef Obj As Object)
        Try
            If Not Obj Is Nothing Then
                Try
                    Obj.close()
                Catch ex As Exception
                    'Debug.Print("Error")
                End Try
                Obj.dispose()
                Obj = Nothing
            End If
        Catch ex As Exception
            Obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class