none
Where is the Microsoft.Office.Interop.Excel held? RRS feed

  • Question

  • I have written a program in VB.net which uses EXCEL to create reports as either print outs or as an Excel File.

    The program works on my PC and Laptop but when I put it on my colleagues PC it throws up this error;

    System.InvalidCastException: Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

    On further investigation I found that Microsoft.Office.Interop.Excel.dll is in directory C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c on my PC and in C:\Windows\assembly on my colleagues machine.

    I tried to add the sub-directories to my colleagues machine, so I could put a copy of the DLL into it but it would not allow me to create the sub-directories.

    I used Microsoft Visual Basic 2017 as the IDE. All machines have Office 365.

    Is there any way to to put the DLL in the same directories as the program files and reference the DLL in Visual Studio?

    Friday, January 10, 2020 11:35 AM

Answers

  • This is an age old issue. Both computers will need to have the exact same version of Excel if you are using early binding rather than late binding.

    One option is to use a package from NuGet here.

    Truly best option is to use OpenXML for Excel which is harder to master yet when using OpenXML best through a library the error you are getting will go away. EPPlus is another option but most examples are in C#.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 11:44 AM
    Moderator
  • Karen

    I have NuGet Package Manager 4.1.0 already installed in my Visual Studio 2017 does that mean I can use OpenXML for Excel already?

    I have looked at a paper on CodeProject about creating Excel sheets and you are correct in that it is a lot harder. Would prefer not to go down that route as the application is in use and we need to get the reports out.

    As I am 72 years old, I write this code as a hobby to help out at my Golf Club. I have only learnt software in the last 5 years.

    In regards to NuGet Package Manager, it should had been installed with Visual Studio unless you are using an older version of Visual Studio. Having NuGet Package Manager allows you (via the link I provided) include a reference for Excel (not OpenXML) which should be consistent between your computer and the other computer.

    In regards to OpenXml, there is a library called SpreadSheetLight, its free with documentation, downside all code samples are in C# but easy to figure out. SpreadSheetLight is a wrapper on OpenXml

    For instance, create a new file, write to a cell then open and read it back.

    Public sub CreateFileOnDesktop()
        Dim fileName As String = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx")
    
        If File.Exists(fileName)Then
            File.Delete(fileName)
        End If
    
        Using doc As New SLDocument()
            doc.SetCellValue("A1","Data in first cell")
            doc.SaveAs(fileName)
        End Using
    
        OpenDesktopFile(fileName)
    
    End Sub
    Public Sub OpenDesktopFile(ByVal pFileName As String)
        Using doc As New SLDocument(pFileName)
            Dim value As String = doc.GetCellValueAsString("A1")
            Console.WriteLine($"A1 is {value}")
        End Using
    End Sub

    Reading cells into a DataTable

    ''' <summary>
    ''' Reading data from cells
    ''' </summary>
    ''' <param name="pFileName">Valid Excel .xlsx file to read</param>
    ''' <param name="pSheetName">Existing Worksheet in pFileName</param>
    ''' <param name="pColumn">Column name e.g. A</param>
    ''' <param name="pHasHeader">Indicates first row has column names</param>
    ''' <returns>
    ''' DataTable with two columns, first column indicates the row index data
    ''' was read from while the second column is the actual data.
    ''' </returns>
    ''' <remarks>
    ''' See method below also
    ''' </remarks>
    Public Function StackOverFlowExample1(
        pFileName As String,
        pSheetName As String,
        pColumn As String,
        Optional ByVal pHasHeader As Boolean = True) As DataTable
    
        Dim startIndex As Integer = 0
        If pHasHeader Then
            startIndex = 1
        End If
    
        Dim dt As New DataTable
    
        dt.Columns.Add(New DataColumn With {.ColumnName = "RowIndex", .DataType = GetType(Integer)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "StringData", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "DoubleData", .DataType = GetType(Double)})
    
        Using sl As New SLDocument(pFileName, pSheetName)
            Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics
            Dim iStartColumnIndex = SLConvert.ToColumnIndex(pColumn)
    
            Dim doubleValue As Double = 0
    
            For row = stats.StartRowIndex + startIndex To stats.EndRowIndex
                If Double.TryParse(sl.GetCellValueAsString(row, iStartColumnIndex), doubleValue) Then
                    dt.Rows.Add(New Object() {row - 1, sl.GetCellValueAsString(row, iStartColumnIndex), doubleValue})
                Else
                    dt.Rows.Add(New Object() {row - 1, sl.GetCellValueAsString(row, iStartColumnIndex)})
                End If
            Next
    
        End Using
    
        Return dt
    
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 12:51 PM
    Moderator
  • For the OpenXml

    Using NuGet Package Manager, use the browse tab, type in DocumentFormat.OpenXml and you will see

    Select version 2.5


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 10:06 PM
    Moderator
  • Karen

    Sorted.

    I had loaded SpreadsheetLight incorrectly.

    I deleted all files associated with SpreadsheetLight and re-installed using NuGet Package manager.

    Thanks for your help not only in solving the problem but also teaching me about NuGet Package manager

    Saturday, January 11, 2020 12:21 PM

All replies

  • This is an age old issue. Both computers will need to have the exact same version of Excel if you are using early binding rather than late binding.

    One option is to use a package from NuGet here.

    Truly best option is to use OpenXML for Excel which is harder to master yet when using OpenXML best through a library the error you are getting will go away. EPPlus is another option but most examples are in C#.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 11:44 AM
    Moderator
  • Karen

    I have NuGet Package Manager 4.1.0 already installed in my Visual Studio 2017 does that mean I can use OpenXML for Excel already?

    I have looked at a paper on CodeProject about creating Excel sheets and you are correct in that it is a lot harder. Would prefer not to go down that route as the application is in use and we need to get the reports out.

    As I am 72 years old, I write this code as a hobby to help out at my Golf Club. I have only learnt software in the last 5 years.

    Friday, January 10, 2020 12:27 PM
  • Karen

    I have NuGet Package Manager 4.1.0 already installed in my Visual Studio 2017 does that mean I can use OpenXML for Excel already?

    I have looked at a paper on CodeProject about creating Excel sheets and you are correct in that it is a lot harder. Would prefer not to go down that route as the application is in use and we need to get the reports out.

    As I am 72 years old, I write this code as a hobby to help out at my Golf Club. I have only learnt software in the last 5 years.

    In regards to NuGet Package Manager, it should had been installed with Visual Studio unless you are using an older version of Visual Studio. Having NuGet Package Manager allows you (via the link I provided) include a reference for Excel (not OpenXML) which should be consistent between your computer and the other computer.

    In regards to OpenXml, there is a library called SpreadSheetLight, its free with documentation, downside all code samples are in C# but easy to figure out. SpreadSheetLight is a wrapper on OpenXml

    For instance, create a new file, write to a cell then open and read it back.

    Public sub CreateFileOnDesktop()
        Dim fileName As String = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx")
    
        If File.Exists(fileName)Then
            File.Delete(fileName)
        End If
    
        Using doc As New SLDocument()
            doc.SetCellValue("A1","Data in first cell")
            doc.SaveAs(fileName)
        End Using
    
        OpenDesktopFile(fileName)
    
    End Sub
    Public Sub OpenDesktopFile(ByVal pFileName As String)
        Using doc As New SLDocument(pFileName)
            Dim value As String = doc.GetCellValueAsString("A1")
            Console.WriteLine($"A1 is {value}")
        End Using
    End Sub

    Reading cells into a DataTable

    ''' <summary>
    ''' Reading data from cells
    ''' </summary>
    ''' <param name="pFileName">Valid Excel .xlsx file to read</param>
    ''' <param name="pSheetName">Existing Worksheet in pFileName</param>
    ''' <param name="pColumn">Column name e.g. A</param>
    ''' <param name="pHasHeader">Indicates first row has column names</param>
    ''' <returns>
    ''' DataTable with two columns, first column indicates the row index data
    ''' was read from while the second column is the actual data.
    ''' </returns>
    ''' <remarks>
    ''' See method below also
    ''' </remarks>
    Public Function StackOverFlowExample1(
        pFileName As String,
        pSheetName As String,
        pColumn As String,
        Optional ByVal pHasHeader As Boolean = True) As DataTable
    
        Dim startIndex As Integer = 0
        If pHasHeader Then
            startIndex = 1
        End If
    
        Dim dt As New DataTable
    
        dt.Columns.Add(New DataColumn With {.ColumnName = "RowIndex", .DataType = GetType(Integer)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "StringData", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "DoubleData", .DataType = GetType(Double)})
    
        Using sl As New SLDocument(pFileName, pSheetName)
            Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics
            Dim iStartColumnIndex = SLConvert.ToColumnIndex(pColumn)
    
            Dim doubleValue As Double = 0
    
            For row = stats.StartRowIndex + startIndex To stats.EndRowIndex
                If Double.TryParse(sl.GetCellValueAsString(row, iStartColumnIndex), doubleValue) Then
                    dt.Rows.Add(New Object() {row - 1, sl.GetCellValueAsString(row, iStartColumnIndex), doubleValue})
                Else
                    dt.Rows.Add(New Object() {row - 1, sl.GetCellValueAsString(row, iStartColumnIndex)})
                End If
            Next
    
        End Using
    
        Return dt
    
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 12:51 PM
    Moderator
  • Karen

    Downloaded  SpreadSheetLight and used one of the examples (converted to VB.net) to create this;

    Imports System
    Imports System.Collections.Generic
    Imports System.Linq
    Imports DocumentFormat.OpenXml
    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.Spreadsheet
    Imports System.Text.RegularExpressions
    Imports System.Text
    Imports SpreadsheetLight


    Public Class Form1
        Dim sheetName, textInput, docName, textColumn As String
        Dim sID, textRow As Integer
        Dim wsp As WorksheetPart
        Dim cell As Cell
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim sl As SLDocument = New SLDocument()
            sl.SetCellValue("A1", True)

            For i As Integer = 1 To 20
                sl.SetCellValue(2, i, i)
            Next

            sl.SetCellValue("B3", 3.14159)
            sl.SetCellValueNumeric(4, 2, "3.14159")
            sl.SetCellValue("C6", "This is at C6!")
            sl.SetCellValue("I6", "Dinner & Dance costs < $10")
            sl.SetCellValue(7, 3, "=SUM(A2:T2)")
            sl.SetCellValue(SLConvert.ToCellReference(7, 4), String.Format("=SUM({0})", SLConvert.ToCellRange(2, 1, 2, 20)))
            sl.SetCellValue("C8", New DateTime(3141, 5, 9))
            Dim style As SLStyle = sl.CreateStyle()
            style.FormatCode = "d-mmm-yyyy"
            sl.SetCellStyle("C8", style)
            sl.SetCellValue(8, 6, "I predict this to be a significant date. Why, I do not know...")
            sl.SetCellValue(9, 4, 456.123789)
            style.FormatCode = "0.000%"
            sl.SetCellStyle(9, 4, style)
            sl.SetCellValue(9, 6, "Perhaps a phenomenal growth in something?")
            sl.SaveAs("HelloWorld.xlsx")
            Console.WriteLine("End of program")
            Console.ReadLine()
        End Sub
    End Class

    However got this error when it tried to execute this line    sl.SaveAs("HelloWorld.xlsx")

    System.TypeLoadException: 'Could not load type 'DocumentFormat.OpenXml.Spreadsheet.SmartTags' from assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.'

    I have downloaded Open XML SDK 2.5 and the latest version of SpreadSheetLight 3.4 which is supposed to work with SDK 2.5.

    Any ideas


    Friday, January 10, 2020 8:56 PM
  • For the OpenXml

    Using NuGet Package Manager, use the browse tab, type in DocumentFormat.OpenXml and you will see

    Select version 2.5


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 10, 2020 10:06 PM
    Moderator
  • Hi Karen

    Referenced DocumentFormat.OpenXML as above. As shown in clip of Visual Studio

    However, still get the same error message. 


    Saturday, January 11, 2020 11:58 AM
  • Karen

    Sorted.

    I had loaded SpreadsheetLight incorrectly.

    I deleted all files associated with SpreadsheetLight and re-installed using NuGet Package manager.

    Thanks for your help not only in solving the problem but also teaching me about NuGet Package manager

    Saturday, January 11, 2020 12:21 PM
  • Your very welcome. One last thing, if you find some methods that are called more than once consider adding the following code module to your project which has some useful methods and you can add your own.

    For example, a new worksheet is needed or a check is needed to see if the worksheet already exists.

    Dim sb As New StringBuilder
    Dim ExcelFile = "Demo.xlsx"
    For Each sheetName As String In SheetNames(ExcelFile)
        sb.AppendLine(sheetName)
    Next
    MessageBox.Show(sb.ToString())
    
    sb.Clear()
    
    If SheetExists(ExcelFile, "Golf Statistics for January") Then
        MessageBox.Show("January exists")
    End If
    
    If Not SheetExists(ExcelFile, "Golf Statistics for April") Then
        AddNewSheet(ExcelFile, "Golf Statistics for April")
        MessageBox.Show("Added April")
    Else
        MessageBox.Show("April found")
    End If
    
    For Each sheetName As String In SheetNames(ExcelFile)
        sb.AppendLine(sheetName)
    Next
    MessageBox.Show(sb.ToString())


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, January 11, 2020 12:53 PM
    Moderator