How to import datatable into Excel without Loop using VB

I am trying to export a SQL table result into Excel using blue prism. Currently it is done by exporting SQL results to collection and then collection to Excel. This is taking a longer time (10 mins) to export ~20K records. This is because the VBO uses for each loop.

I am trying to use some customized VB code to load the collection into excel using bulk update.

Can anyone help me on this?

I tried the below code but its not working

' Get to the cell
Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim origin As Object = ws.Range(cellref, cellref)
Dim cell As Object = origin
Dim colInd As Integer = 0, rowCount As Integer, rowInd As Integer = 0 ' Offsets from the origin cell

' Deal with the column names first
If includecolnames Then
    For Each col As DataColumn In Collection.Columns
        Try
            cell = origin.Offset(rowInd, colInd)
        Catch ex As Exception ' Hit the edge.
            Exit For
        End Try
        SetProperty(cell, "Value", col.ColumnName)
        colInd += 1
    Next
    rowInd += 1
End If

rowCount = Collection.Rows.Count
xlRange = ws.Range(cellref & ":H" & rowCount)
xlRange.Value = Collection

Error that I am getting is: Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

Solution

Thanks for your answers. I have added a new page called Write Collection - Fast and added code stage in Blue Prism MS Excel VBO. Pasted the below code

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim sqlCon As New ADODB.Connection
Dim recordSet As New ADODB.Recordset
Dim iCol As Integer

    sqlCon = New ADODB.Connection
    sqlCon.ConnectionString = "driver={SQL Server};server=xxx\SQLEXPRESS;uid=zzzz;pwd=yyyy;database=testData"
    sqlCon.ConnectionTimeout = 30
    sqlCon.Open


    recordSet.Open (SQL, sqlCon)

    For iCol = 0 To recordSet.Fields.Count - 1
        ws.Cells(1, iCol + 1).Value = recordSet.Fields(iCol).Name
    Next

    ws.Range("A2").CopyFromRecordset(recordSet)

    recordSet.Close
    sqlCon.Close