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

Speed up Excel Data Read

P: 2
Hey,

I am trying to read data from an excel spread sheet then combine the data into a combo box. My code works the problem is that it is very slow because of the amount of data it has to process. Is there a way to speed up the process of extracting the data from excel. It takes about 30 seconds to load the form right now.

Thanks

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  3.         'Dim local Variables
  4.         Dim objSheet As Excel.Worksheet
  5.         Dim objWB As Excel.Workbook
  6.         Dim Srow As Integer
  7.         Dim Scol As Integer
  8.         Dim ESrow As Integer
  9.         Dim EScol As Integer
  10.         Dim FSrow As Integer
  11.         Dim FScol As Integer
  12.         Dim DErow As Integer
  13.         Dim DEcol As Integer
  14.         Dim FErow As Integer
  15.         Dim FEcol As Integer
  16.  
  17.         'Initiate Progress Bar
  18.         SecondForm.Show()
  19.         SecondForm.ProgressBar1.Maximum = 13292
  20.         SecondForm.ProgressBar1.Step = 1
  21.  
  22.         'open Excel Site Code list
  23.         objApp = New Excel.Application
  24.         objApp.Visible = False
  25.         objWB = objApp.Workbooks.Open("D:/Support Files/SITECODE.xls")
  26.         objSheet = objWB.Worksheets(1)
  27.  
  28.         'Get Site Codes
  29.         For Srow = 2 To 3302
  30.             For Scol = 1 To 1
  31.                 SITECODE.Items.Add(objApp.Worksheets(1).CELLS(Srow, Scol).Value)
  32.                 SecondForm.ProgressBar1.PerformStep()
  33.                 SecondForm.ProgressBar1.Refresh()
  34.             Next
  35.         Next
  36.  
  37.         'Get English Site Names
  38.         For ESrow = 2 To 3302
  39.             For EScol = 2 To 2
  40.                 ESNAME.Items.Add(objApp.Worksheets(1).CELLS(ESrow, EScol).Value)
  41.                 SecondForm.ProgressBar1.PerformStep()
  42.                 SecondForm.ProgressBar1.Refresh()
  43.             Next
  44.         Next
  45.  
  46.         'Get French Site Names
  47.         For FSrow = 2 To 3302
  48.             For FScol = 3 To 3
  49.                 FSNAME.Items.Add(objApp.Worksheets(1).CELLS(FSrow, FScol).Value)
  50.                 SecondForm.ProgressBar1.PerformStep()
  51.                 SecondForm.ProgressBar1.Refresh()
  52.             Next
  53.         Next
  54.  
  55.         'Combine the returned values and populate ETITLE1 & FTITLE1 with the results
  56.         For x = 0 To 3300
  57.             ETITLE1.Items.Add(CStr(SITECODE.Items(x) & " - " & (ESNAME.Items(x))))
  58.             FTITLE1.Items.Add(CStr(SITECODE.Items(x) & " - " & (FSNAME.Items(x))))
  59.             SecondForm.ProgressBar1.PerformStep()
  60.             SecondForm.ProgressBar1.Refresh()
  61.         Next x
  62.  
  63.         'Get English drawing types and populate ETITLE4 with the results
  64.         For derow = 2 To 43
  65.             For decol = 4 To 4
  66.                 ETITLE4.Items.Add(objApp.Worksheets(1).CELLS(derow, decol).Value)
  67.                 SecondForm.ProgressBar1.PerformStep()
  68.                 SecondForm.ProgressBar1.Refresh()
  69.             Next
  70.         Next
  71.  
  72.         'Get French drawing types and populate FTITLE4 with the results
  73.         For FErow = 2 To 43
  74.             For FEcol = 5 To 5
  75.                 FTITLE4.Items.Add(objApp.Worksheets(1).CELLS(FErow, FEcol).Value)
  76.                 SecondForm.ProgressBar1.PerformStep()
  77.                 SecondForm.ProgressBar1.Refresh()
  78.             Next
  79.         Next
  80.  
  81.         'Close Progress Bar Form 
  82.         SecondForm.Hide()
  83.         'close out excel 
  84.         Threading.Thread.Sleep(1000)
  85.  
  86.         Try
  87.             ReleaseComObject(objSheet)
  88.             If objWB IsNot Nothing Then
  89.                 objWB.Close(SaveChanges:=False)
  90.                 ReleaseComObject(objWB)
  91.             End If
  92.             If objApp IsNot Nothing Then
  93.                 objApp.Quit()
  94.                 ReleaseComObject(objApp)
  95.             End If
  96.         Finally
  97.             GC.WaitForPendingFinalizers()
  98.             GC.Collect()
  99.         End Try
  100.  
  101.     End Sub
  102.  
Apr 7 '11 #1
Share this Question
Share on Google+
2 Replies


Guido Geurs
Expert 100+
P: 767
Working with Excel is much faster when you put the data in an array , do the processes and put the results in a second array, dump the result-array in the sheet.

Sheet to Array=
Expand|Select|Wrap|Line Numbers
  1. Dim ARRAYDATA As Variant ‘with range=> array must always be VARIANT
  2. Sheets(1).Select
  3. ' set sheet1 in array
  4. ‘                       ( |--------------rows--------------|-----------cols-------------------| )
  5.    ARRAYDATA = Range("A1").Resize(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)
Array to sheet:
Expand|Select|Wrap|Line Numbers
  1. Sheets(2).Select
  2. ' copy Array to range
  3.    Range("A1").Resize(UBound(ARRAYDATA), UBound(ARRAYDATA, 2)) = ARRAYDATA
Apr 8 '11 #2

P: 2
Hey thanks for your response. I ended up going this way and got my application to work quite a bit faster (down to around 6 seconds.
Apr 12 '11 #3

Post your reply

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