CODEWITHSUNDEEP
excelvbaado
pascal sautot
pascal sautot

Reputation: 405

what is the fastest way to assign data array items to range values

I am developing an excel client to a SQL server data base.

When I select data from the data base to the client records are saved in arrays. These arrays are then "injected" into Excel sheets using an array of variant.

This technique is far faster than assigning individual cell values BUT seems to fail in some specific context.

When the data value is vbNullString the range cell corresponding to the item is not assigned. Worst if the first item is vbNullString, none of the items are saved to the range. My guess is that the vbNullString is translated to null as a variant and the variant array has the wrong dimension.

Is there any other explanation ?

For instance 

ByRef loTable As ListObject,
ByRef sItems() As String

are parameters of a sub executing the following code where loTable is an Excel Table (ListObject) and sItems the data I want to assign to the range of the Excel Table loTable 

Dim lCount as Long
Dim vItems() as Variant
lCount = UBound(sItems)-LBound(sItems)+1
'size vItems as a 2D array - required to fetch a range
If (lCount > 0) Then ReDim vItems(1 To lCount, 1 To 1)
'copy string to variant
Dim i As Integer
For i = LBound(sItems) To UBound(sItems)
    vItems(i, 1) = sItems(i)
Next i
Dim olrs As ListRows
Set olrs = loTable.ListRows
With loTable
    If (lCount > 0) Then
        If (Not (.DataBodyRange Is Nothing)) Then .DataBodyRange.Delete
        olrs.Add
        If (lCount = 1) Then
            olrs(olrs.Count).Range.Cells(1, 1).Value = sItems(LBound(sItems))
        Else
            .DataBodyRange.Resize(lCount, 1).Value = vItems
        End If
    Else
        .DataBodyRange.Delete
    End If
End With 'loTable

I come to the question : have you tried such a range (of a table) value assignment and how to cope with null items in an array for a fast assignment. Looping over the array items and adding one by one just takes for ever.

Upvotes: 0

Views: 696

Answers (1)

Brian
Brian

Reputation: 2108

Would something like this help?

'Declarations section
Private Const gsPROVIDER as String = [Provider]
Private Const gsDATA_SOURCE as String = [Source]
Private Const gsINITIAL_CATALOG as String = [Catalog]
Private Const gsUSER_ID as String = [User ID]
Private Const gsPASSWORD as String = [Password]

Private Sub Test
    Dim rsData As Object
    Dim sConnect As String
    Dim sSQL As String

    sConnect = "Provider=" & gsPROVIDER & _
               "Data Source=" & gsDATA_SOURCE & _
               "Initial Catalog=" & gsINITIAL_CATALOG & _
               "User ID=" & gsUSER_ID & _
               "Password=" & gsPASSWORD
    sSQL = 'SQL in a string

    Set rsData = CreateObject("ADODB.Recordset")
    rsData.Open sSQL, sConnect

    If Not rsData.EOF Then
        Worksheets(1).ListObjects(loTable).DataBodyRange.CopyFromRecordset rsData
    Else
        MsgBox "No data found!", vbCritical, "Error!"
    End If

    rsData.Close
    Set rsData = Nothing
End Sub

Upvotes: 2

Related Questions