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
- Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
- 'Dim local Variables
- Dim objSheet As Excel.Worksheet
- Dim objWB As Excel.Workbook
- Dim Srow As Integer
- Dim Scol As Integer
- Dim ESrow As Integer
- Dim EScol As Integer
- Dim FSrow As Integer
- Dim FScol As Integer
- Dim DErow As Integer
- Dim DEcol As Integer
- Dim FErow As Integer
- Dim FEcol As Integer
- 'Initiate Progress Bar
- SecondForm.Show()
- SecondForm.ProgressBar1.Maximum = 13292
- SecondForm.ProgressBar1.Step = 1
- 'open Excel Site Code list
- objApp = New Excel.Application
- objApp.Visible = False
- objWB = objApp.Workbooks.Open("D:/Support Files/SITECODE.xls")
- objSheet = objWB.Worksheets(1)
- 'Get Site Codes
- For Srow = 2 To 3302
- For Scol = 1 To 1
- SITECODE.Items.Add(objApp.Worksheets(1).CELLS(Srow, Scol).Value)
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next
- Next
- 'Get English Site Names
- For ESrow = 2 To 3302
- For EScol = 2 To 2
- ESNAME.Items.Add(objApp.Worksheets(1).CELLS(ESrow, EScol).Value)
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next
- Next
- 'Get French Site Names
- For FSrow = 2 To 3302
- For FScol = 3 To 3
- FSNAME.Items.Add(objApp.Worksheets(1).CELLS(FSrow, FScol).Value)
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next
- Next
- 'Combine the returned values and populate ETITLE1 & FTITLE1 with the results
- For x = 0 To 3300
- ETITLE1.Items.Add(CStr(SITECODE.Items(x) & " - " & (ESNAME.Items(x))))
- FTITLE1.Items.Add(CStr(SITECODE.Items(x) & " - " & (FSNAME.Items(x))))
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next x
- 'Get English drawing types and populate ETITLE4 with the results
- For derow = 2 To 43
- For decol = 4 To 4
- ETITLE4.Items.Add(objApp.Worksheets(1).CELLS(derow, decol).Value)
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next
- Next
- 'Get French drawing types and populate FTITLE4 with the results
- For FErow = 2 To 43
- For FEcol = 5 To 5
- FTITLE4.Items.Add(objApp.Worksheets(1).CELLS(FErow, FEcol).Value)
- SecondForm.ProgressBar1.PerformStep()
- SecondForm.ProgressBar1.Refresh()
- Next
- Next
- 'Close Progress Bar Form
- SecondForm.Hide()
- 'close out excel
- Threading.Thread.Sleep(1000)
- Try
- ReleaseComObject(objSheet)
- If objWB IsNot Nothing Then
- objWB.Close(SaveChanges:=False)
- ReleaseComObject(objWB)
- End If
- If objApp IsNot Nothing Then
- objApp.Quit()
- ReleaseComObject(objApp)
- End If
- Finally
- GC.WaitForPendingFinalizers()
- GC.Collect()
- End Try
- End Sub