none
Help with Office365/ Excel and VB.Net2019 RRS feed

  • Question

  • Hello Guys.

    First I would like to say thank you for all your support.

    I need some help. Im using VB2019 and Office365/Excel. I need to get all the information from different files in excel (one by one) and drop this information in DataGridView then I'm going to save that information in SQLServer(I can do that part VB.net -SqlServer) .
    My main concern is the connection between excel and vb.net. In additional I need to start to read excel files after a3. (row#2) and

    with the row#1. I need to save that information in other control.
    Example from my excel sheet.

    1. Equipment Check List for MB11200605P/11-3 south renovation
    2. LineNo PartNum Name ManuNum Mfg Name Description Quantity Original Cost Cost Sell Price DateReqd PONumber

    Thanks


    Please I need help.


    MB

    Sunday, September 1, 2019 2:56 PM

Answers

  • I would recommend SpreadSheetLight, a free library via NuGet. I have some samples here. Bottom line is using Excel automation is old school, it's fragile while libraries such as SpreadSheetLight work off Open Office API.

    Example opening via automation and reading a few cells.

    Option Strict On 
    Option Infer Off 
     
    Imports System.Runtime.InteropServices 
    Imports Excel = Microsoft.Office.Interop.Excel 
     
    Public Module OpenWorkBookSimple 
        Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String) 
     
            If IO.File.Exists(FileName) Then 
     
                Dim Proceed As Boolean = False 
     
                Dim xlApp As Excel.Application = Nothing 
                Dim xlWorkBooks As Excel.Workbooks = Nothing 
                Dim xlWorkBook As Excel.Workbook = Nothing 
                Dim xlWorkSheet As Excel.Worksheet = Nothing 
                Dim xlWorkSheets As Excel.Sheets = Nothing 
                Dim xlCells As Excel.Range = Nothing 
     
                xlApp = New Excel.Application 
                xlApp.DisplayAlerts = False 
                xlWorkBooks = xlApp.Workbooks 
                xlWorkBook = xlWorkBooks.Open(FileName) 
     
                xlApp.Visible = False 
                xlWorkSheets = xlWorkBook.Sheets 
     
                ' 
                ' For/Next finds our sheet 
                ' 
                For x As Integer = 1 To xlWorkSheets.Count 
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet) 
     
                    If xlWorkSheet.Name = SheetName Then 
                        Proceed = True 
                        Exit For 
                    End If 
     
                    Marshal.FinalReleaseComObject(xlWorkSheet) 
                    xlWorkSheet = Nothing 
     
                Next 
                If Proceed Then 
                    Dim sb As New Text.StringBuilder 
     
                    Dim Cells As String() = {"A1", "B2", "B3", "B4"} 
                    For Each cell As String In Cells 
                        Try 
                            xlCells = xlWorkSheet.Range(cell) 
                            sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value)) 
                        Catch ex As Exception 
                            ReleaseExcelObject(xlCells) 
                        End Try 
                    Next 
                Else 
                    ' sheet not found 
                End If 
     
                xlWorkBook.Close() 
                xlApp.UserControl = True 
                xlApp.Quit() 
     
                ReleaseExcelObject(xlCells) 
                ReleaseExcelObject(xlWorkSheets) 
                ReleaseExcelObject(xlWorkSheet) 
                ReleaseExcelObject(xlWorkBook) 
                ReleaseExcelObject(xlWorkBooks) 
                ReleaseExcelObject(xlApp) 
            Else 
                ' file does not exists 
            End If 
        End Sub 
        Private Sub ReleaseExcelObject(ByVal excelObject As Object) 
            Try 
                If excelObject IsNot Nothing Then 
                    Marshal.ReleaseComObject(excelObject) 
                    excelObject = Nothing 
                End If 
            Catch ex As Exception 
                excelObject = Nothing 
            End Try 
        End Sub 
    End Module 

    Same operation in SpreadSheetLight.

    Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String) 
        Using sl As New SLDocument(FileName, SheetName) 
            Dim sb As New Text.StringBuilder 
            Dim Cells As String() = {"A1", "B2", "B3", "B4"} 
            For Each cell As String In Cells 
                sb.AppendLine(String.Format("{0} = '{1}'", cell, sl.GetCellValueAsString(cell))) 
            Next 
        End Using 
    End Sub


    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

    • Marked as answer by Marcos Bolivar Sunday, September 1, 2019 8:32 PM
    Sunday, September 1, 2019 8:22 PM
    Moderator
  • Hello Karen. 

    I almost ready with my project and I would to say thank you for the las answer. Its working for me. 

    Now I have another question for you. 

    I'm trying to use SpreadSheetLight to transfer data from VB.net to Excel. I already have the spreadsheet reference and Im using the following function:

            Dim s1 As New SLDocument
            s1 = New SLDocument(File_ExcelPath)
            If s1.SetCellValue(2, 9, irow) = True Then
                s1.Dispose()

    but I got the following error in vb.net

    Severity Code Description Project File Line Suppression State
    Error BC30652 Reference required to assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' containing the type 'InlineString'. Add one to your project. InventoryV04 C:\Users\marcos.bolivar\source\repos\InventoryV04\Form25.vb 157 Active

    and I already have the reference in my project. 

    I appreciate any help please.

     


    MB

    Hello,

    I've only seen this happen when DocumentFormat.OpenXml was installed with the incorrect version e.g. if you installed the latest version rather than 2.5.0 then this will happen. If you installed 2.9.1 then go back in to NuGet Package Manager and select the drop down and select 2.5.0 and hit install.


    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

    • Marked as answer by Marcos Bolivar Sunday, September 29, 2019 5:19 PM
    Sunday, September 29, 2019 9:37 AM
    Moderator

All replies

  • I would recommend SpreadSheetLight, a free library via NuGet. I have some samples here. Bottom line is using Excel automation is old school, it's fragile while libraries such as SpreadSheetLight work off Open Office API.

    Example opening via automation and reading a few cells.

    Option Strict On 
    Option Infer Off 
     
    Imports System.Runtime.InteropServices 
    Imports Excel = Microsoft.Office.Interop.Excel 
     
    Public Module OpenWorkBookSimple 
        Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String) 
     
            If IO.File.Exists(FileName) Then 
     
                Dim Proceed As Boolean = False 
     
                Dim xlApp As Excel.Application = Nothing 
                Dim xlWorkBooks As Excel.Workbooks = Nothing 
                Dim xlWorkBook As Excel.Workbook = Nothing 
                Dim xlWorkSheet As Excel.Worksheet = Nothing 
                Dim xlWorkSheets As Excel.Sheets = Nothing 
                Dim xlCells As Excel.Range = Nothing 
     
                xlApp = New Excel.Application 
                xlApp.DisplayAlerts = False 
                xlWorkBooks = xlApp.Workbooks 
                xlWorkBook = xlWorkBooks.Open(FileName) 
     
                xlApp.Visible = False 
                xlWorkSheets = xlWorkBook.Sheets 
     
                ' 
                ' For/Next finds our sheet 
                ' 
                For x As Integer = 1 To xlWorkSheets.Count 
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet) 
     
                    If xlWorkSheet.Name = SheetName Then 
                        Proceed = True 
                        Exit For 
                    End If 
     
                    Marshal.FinalReleaseComObject(xlWorkSheet) 
                    xlWorkSheet = Nothing 
     
                Next 
                If Proceed Then 
                    Dim sb As New Text.StringBuilder 
     
                    Dim Cells As String() = {"A1", "B2", "B3", "B4"} 
                    For Each cell As String In Cells 
                        Try 
                            xlCells = xlWorkSheet.Range(cell) 
                            sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value)) 
                        Catch ex As Exception 
                            ReleaseExcelObject(xlCells) 
                        End Try 
                    Next 
                Else 
                    ' sheet not found 
                End If 
     
                xlWorkBook.Close() 
                xlApp.UserControl = True 
                xlApp.Quit() 
     
                ReleaseExcelObject(xlCells) 
                ReleaseExcelObject(xlWorkSheets) 
                ReleaseExcelObject(xlWorkSheet) 
                ReleaseExcelObject(xlWorkBook) 
                ReleaseExcelObject(xlWorkBooks) 
                ReleaseExcelObject(xlApp) 
            Else 
                ' file does not exists 
            End If 
        End Sub 
        Private Sub ReleaseExcelObject(ByVal excelObject As Object) 
            Try 
                If excelObject IsNot Nothing Then 
                    Marshal.ReleaseComObject(excelObject) 
                    excelObject = Nothing 
                End If 
            Catch ex As Exception 
                excelObject = Nothing 
            End Try 
        End Sub 
    End Module 

    Same operation in SpreadSheetLight.

    Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String) 
        Using sl As New SLDocument(FileName, SheetName) 
            Dim sb As New Text.StringBuilder 
            Dim Cells As String() = {"A1", "B2", "B3", "B4"} 
            For Each cell As String In Cells 
                sb.AppendLine(String.Format("{0} = '{1}'", cell, sl.GetCellValueAsString(cell))) 
            Next 
        End Using 
    End Sub


    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

    • Marked as answer by Marcos Bolivar Sunday, September 1, 2019 8:32 PM
    Sunday, September 1, 2019 8:22 PM
    Moderator
  • Hello Karen.

    Thank you so much let me try this way. Iwill let you know.


    MB

    Sunday, September 1, 2019 8:31 PM
  • Hello Karen. 

    I almost ready with my project and I would to say thank you for the las answer. Its working for me. 

    Now I have another question for you. 

    I'm trying to use SpreadSheetLight to transfer data from VB.net to Excel. I already have the spreadsheet reference and Im using the following function:

            Dim s1 As New SLDocument
            s1 = New SLDocument(File_ExcelPath)
            If s1.SetCellValue(2, 9, irow) = True Then
                s1.Dispose()

    but I got the following error in vb.net

    Severity Code Description Project File Line Suppression State
    Error BC30652 Reference required to assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' containing the type 'InlineString'. Add one to your project. InventoryV04 C:\Users\marcos.bolivar\source\repos\InventoryV04\Form25.vb 157 Active

    and I already have the reference in my project. 

    I appreciate any help please.

     


    MB

    Sunday, September 29, 2019 3:26 AM
  • Hello Karen. 

    I almost ready with my project and I would to say thank you for the las answer. Its working for me. 

    Now I have another question for you. 

    I'm trying to use SpreadSheetLight to transfer data from VB.net to Excel. I already have the spreadsheet reference and Im using the following function:

            Dim s1 As New SLDocument
            s1 = New SLDocument(File_ExcelPath)
            If s1.SetCellValue(2, 9, irow) = True Then
                s1.Dispose()

    but I got the following error in vb.net

    Severity Code Description Project File Line Suppression State
    Error BC30652 Reference required to assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' containing the type 'InlineString'. Add one to your project. InventoryV04 C:\Users\marcos.bolivar\source\repos\InventoryV04\Form25.vb 157 Active

    and I already have the reference in my project. 

    I appreciate any help please.

     


    MB

    Hello,

    I've only seen this happen when DocumentFormat.OpenXml was installed with the incorrect version e.g. if you installed the latest version rather than 2.5.0 then this will happen. If you installed 2.9.1 then go back in to NuGet Package Manager and select the drop down and select 2.5.0 and hit install.


    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

    • Marked as answer by Marcos Bolivar Sunday, September 29, 2019 5:19 PM
    Sunday, September 29, 2019 9:37 AM
    Moderator
  • Thank you very much Karen. It work in my project.

    MB

    Sunday, September 29, 2019 5:19 PM
  • Thank you very much Karen. It work in my project.

    MB

    Excellent :-)

    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

    Sunday, September 29, 2019 11:57 PM
    Moderator