[Excel/VBA]: How to resolve -2147467259 error while populating combobox?

[prashantdixit]

Hi,

I have imported huge log file (35 MB size) into a worksheet. One of its column contain some data.

There is a Main Sheet which contains "ComboBoxStartT ime" ActiveX Combo control.

I am populating data from a range into this combobox. The number of rows containing data are huge because of size of file (let say: 506390 or more) However i am getting an error:

Run-time error '-2147467259 (80004005)

The code for populating combobox is:

Public Sub FillStartTimeComboBox(Datasheet As Worksheet, ByVal FirstDataRow As Long)

Application.ScreenUpdating = False
    Dim myRange As Range
    Dim r As Range
    Dim LastDataRow As Long
    Dim LValue As String

    'Sheet1.ComboBoxStartTime.Clear
    Datasheet.Activate
    LastDataRow = Cells(1000000, 1).End(xlUp).row
    'Set the range of cells, whose values will be put into the combobox
    Set myRange = Range(Cells(FirstDataRow, 1), Cells(LastDataRow, 1))

    'work through each cell in the range and put the value in the Spectrum combobox.
    Cells(FirstDataRow, 1).Select
        For Each r In myRange
            'add cell value to combobox list if it is not a blank cell.
            If r.Value <> "" Then
                LValue = Format(r, "dd/mm/yyyy hh:mm:ss")
                Sheet1.ComboBoxStartTime.AddItem LValue
            End If
        Next r
 Application.ScreenUpdating = True
End Sub

Error is coming at line:

Sheet1.ComboBoxStartTime.AddItem LValue

Any help would be highly appreciated

Thanks
Prashant Dixit

[MMcCarthy]

Put a code breakpoint on this line and try to figure out if the code runs through and falls over only at a certain value or if it never runs at all. Also, check out that LValue looks OK.

Also, when this error occurs there is usually something written after it to clarify the error.

[prashantdixit]

I have done it and its giving error at Row ID: 121100 whereas file contain around 5000000

All data upto row id 121099 is populating in combobox but not afterwards

And the Error message says
Runtime Error: '-2147467259 (80004005)
Unspecified Error.

It is problem with the system resource or with huge amount of data?

Thanks
Prashant

[MMcCarthy]

Check out what value is stored in LValue when the code breaks. You should be able to do this by hovering over the LValue part of line #20. The reason I am asking is because the value you are looking at is a timestamp and if for some reason it doesn't recognise it as a valid date it may fall over.

It's also possible that you are just limited on the number of values you can store in a combobox. Excel has a number of limitations. One of those is that you can't visibly see more than ~65,500 records. It's possible that excel can't store more than 121,099. I find it suspicious that the number seems to be double the visible capacity.

In all honesty I've never tried dealing with anywhere near this number of records in Excel. It's not really designed to handle it.

[NeoPa]

Prashant, You can't fill a ComboBox with that many entries. It makes no sense as it becomes totally unusable the more you have and even 50 entries makes it almost useless.

I doubt the control was ever designed to stretch as far as you're trying to take it.