By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

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

P: 36
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 "ComboBoxStartTime" 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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub FillStartTimeComboBox(Datasheet As Worksheet, ByVal FirstDataRow As Long)
  2.  
  3. Application.ScreenUpdating = False
  4.     Dim myRange As Range
  5.     Dim r As Range
  6.     Dim LastDataRow As Long
  7.     Dim LValue As String
  8.  
  9.     'Sheet1.ComboBoxStartTime.Clear
  10.     Datasheet.Activate
  11.     LastDataRow = Cells(1000000, 1).End(xlUp).row
  12.     'Set the range of cells, whose values will be put into the combobox
  13.     Set myRange = Range(Cells(FirstDataRow, 1), Cells(LastDataRow, 1))
  14.  
  15.     'work through each cell in the range and put the value in the Spectrum combobox.
  16.     Cells(FirstDataRow, 1).Select
  17.         For Each r In myRange
  18.             'add cell value to combobox list if it is not a blank cell.
  19.             If r.Value <> "" Then
  20.                 LValue = Format(r, "dd/mm/yyyy hh:mm:ss")
  21.                 Sheet1.ComboBoxStartTime.AddItem LValue
  22.             End If
  23.         Next r
  24.  Application.ScreenUpdating = True
  25. End Sub
  26.  

Error is coming at line:
Expand|Select|Wrap|Line Numbers
  1. Sheet1.ComboBoxStartTime.AddItem LValue
Any help would be highly appreciated

Thanks
Prashant Dixit
Oct 1 '10 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 1 '10 #2

P: 36
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
Oct 1 '10 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 4 '10 #4

NeoPa
Expert Mod 15k+
P: 31,766
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.
Oct 28 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.