Why is my app so slow when writing from VB to Excel

abc12345

20 pts.

Tags:

Microsoft Excel

Visual Basic

What I use: An app where following procedure has to be run approx 100.000 times... it takes 5 minutes! It should go faster.

Question=how?

Dim Record as string Dim oExcel As Microsoft.Office.Interop.Excel.Application Dim EXCBook As Object, Range as object oExcel = New Microsoft.Office.Interop.Excel.Application EXCBook = oExcel.Workbooks.Open(TxtFolder.Text & "FBL.xlt") Record = strLine & vbTab & strSort & vbTab & Date 'sets the values for column 1, 2 & 3 in the Row Clipboard.SetText(Record.ToString) Range = EXCBook.Worksheets(Page).cells(Row, 1) EXCBook.Worksheets(Blad).Paste()

Software/Hardware used:
vb studio 2005 & excel 2003

Asked: November 18, 2012 2:37 PM Last updated: November 19, 2012 1:12 PM

Answer Wiki

Last updated: November 27, 2012 3:51 PM GMT

thongtarget21485 pts.
History
Contributors
- Ordered by most recent
- thongtarget21485 pts.
- Michael Tidmarsh62,260 pts.
- Ben Rubenstein11,255 pts.
- abc1234520 pts.

Thanks. We'll let you know when a new response is added.

Hopefully abc can provide us with additional details.

Discuss This Question: 3 Replies

Ben Rubenstein Nov 18, 2012 3:49 PM GMT

Welcome to ITKE, abc. One of our VB experts should be able to help soon.

In the meantime, make sure to fill out your profile fully with your picture and bio, and check out the FAQ page for more on how our community works.

11,255 pointsBadges:

report
TomLiotta Nov 18, 2012 6:41 PM GMT

...following procedure has to be run approx 100.000 times... it takes 5 minutes! Does that mean that you expect to invoke and run that code more than 333 times per second? Can you describe the environment? What is it running on? How is it called? Tom

125,585 pointsBadges:

report
ToddN2000 Nov 19, 2012 4:16 PM GMT

I never sent data to an Excel spreadsheet myself but why not send as a dataset/datatable instead of on a record basis?

100,375 pointsBadges:

report