How to Import Data Into Heavy Excel File

Question

Goal

Import about 100+ lines of data into a worksheet. Quickly.

Current Problem

The Excel file isn't very large (not even 1MB). Although, we use this Excel file to communicate with SolidEdge which makes it very heavy.

At the moment, it takes about 60 seconds to populate the 100 lines of data. Don't get me wrong, that isn't very long. Whereas I tested it with a new and empty Excel file and it took less than 1 second to populate the data.

Code

Here's my code incase I did something stupid in there:

Private Sub PopulateExcel()
    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 Proceed As Boolean = False
    Dim RowIndex As Integer = 2
    Dim counter As Integer = 0

    xlApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
    xlWorkBooks = xlApp.Workbooks

    For Each wb As Excel.Workbook In xlWorkBooks
        If wb.Name.Contains("301-AAAA-00X") Then
            xlWorkBook = wb
            xlWorkSheets = xlWorkBook.Sheets
            Exit For
        End If
    Next

    If xlWorkSheets IsNot Nothing Then
        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
            If xlWorkSheet.Name = "ImportSheet" Then
                Proceed = True
                Exit For
            End If
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next
    End If

    If Proceed Then
        tspbProgress.Value = 0
        tspbProgress.Maximum = dic_Vars.Count
        tspbProgress.Visible = True

        For Each key As KeyValuePair(Of String, String) In dic_Vars 'Contains all my the data
            tsslStatus.Text = "Populating Excel: " & key.Key & " | " & key.Value
            xlWorkSheet.Cells(RowIndex, 2).value = key.Key
            xlWorkSheet.Cells(RowIndex, 3).value = key.Value
            RowIndex += 1
            IncProg()
        Next

        tspbProgress.Visible = False
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    End If
End Sub

Private Sub ReleaseComObject(ByRef obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

Conclusion

I was thinking about creating a new Excel file, importing the data there, and just doing a copy / paste to the real Excel file that we use.

Any suggestions?

Answer 1

Good thank you to Byron Wall who helped me find the answer. I now input my data using an array instead of iterating through each cell.

I create my array and populate it depending on how large my dictionary of variables is. I then create a new range using the Resize() method seen below.

Once that's done, everything is populated in an instant!

    Dim arrNames(,) As String = New String(AscW(ChrW(dic_Vars.Count)), 1) {}
    Dim arrValues(,) As String = New String(AscW(ChrW(dic_Vars.Count)), 1) {}
    Dim i As Integer = 0

    For Each key As KeyValuePair(Of String, String) In dic_Vars
        arrNames(i, 0) = key.Key
        arrValues(i, 0) = key.Value
        i += 1
    Next

    If Proceed Then
        Dim r As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.Range("B2").Resize(arrNames.GetLength(0))
        Dim r2 As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.Range("C2").Resize(arrValues.GetLength(0))
        r.Value2 = arrNames
        r2.Value2 = arrValues

        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    End If