473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Speed up Excel Data Read

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
2 2700
Guido Geurs
767 Expert 512MB
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
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

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

Similar topics

0
by: Teddy | last post by:
Language: C Excel Reading Method: ExecuteReade I am currently running into a challenge reading excel data when datatype formats are different in the SAME column. It seems like datatype (in the...
1
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns...
2
by: amitshinde02 | last post by:
Hi Experts, I have been struggling on this problem since last one month. I have a huge Excel sheet (Not well formatted) with around 10000 records or more. with around 60 fields. I want to...
4
by: Peter | last post by:
Does anyone know a way to import excel data very fast? I have spreadsheet data that has to be processed, the spreadsheet has 256 columns and 20,000 rows of data. That's 5,120,000 cells. The...
1
by: ShailShin | last post by:
Hi All, I have to convert Excel data to MSAcess .MDB file using VB.NET. VB.NET Code read the Excel file and write it to .MDB file. For the same I have below code, but I am stuck at the writing it...
3
by: Bharathi Kumar | last post by:
Hi, In my windows application, I have created excel.application object and read the excel data. I saved the data to sql server also. The problem Iam facing is when I create an instance of...
3
by: Shilpa | last post by:
Hi, I want to read excel data cell by cell using C#. I do not want to use a connection and say "select * from Sheet1" I do not want to use the COM Excel 11 library. I do not want to create an...
0
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may...
1
by: pompair | last post by:
Hi, I've been implementing a program that populates winforms DataGridView from an excel sheet (.xlsx-file). The app works ok. Now I'm refactoring it and I've got a feeling that databinding...
1
by: cindy7 | last post by:
how can i read excel data and import into sql using cfm??? I have a CFM form for user to import excel file into database and save file in the folder at server location. I would like to read that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.