Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Apr 08, 2020 3:02 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Move Array Data to Excel
PostPosted: Sat May 26, 2012 8:31 am 
Offline

Joined: Tue Oct 02, 2007 6:47 am
Posts: 32
Hello Folks,

I have a simple method to move a 16000 line array over to Excel. So, the first value lands in cell A1 and the last line lands in cell A16000, like this:

for i = 0 to ubound(datatable)-1
excel.Range("A" + Str(i + 1)).Value = datatable(i)
next


This works, but the problem is it takes about 3 minutes! I've tried adding line breaks 'chr(10)' after each step in the array and then building a single variable to avoid the loop. This is much faster, but all of the data ends up in one cell (A1) and that's not quite what I need.

Anyone faced this or have any thoughts?

Thanks, Sean


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Sat May 26, 2012 9:46 am 
Offline

Joined: Fri Dec 01, 2006 3:09 pm
Posts: 650
Location: Tennessee, USA
Have you tried writing the lines to a text file where each line in the array is a line in the file, and then telling Excel to open that file?


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Sat May 26, 2012 7:59 pm 
Offline
User avatar

Joined: Wed May 10, 2006 2:42 pm
Posts: 2985
Location: Germany
Our MBS XL Plugin can read/write excel files. Maybe you try that one?

Greetings
Christian

_________________
See you in Orlando, Florida for Real World 2013
More details and registration here:
http://www.realsoftware.com/community/realworld.php


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Mon May 28, 2012 11:24 pm 
Offline

Joined: Mon Jan 02, 2006 11:43 am
Posts: 172
Location: Calgary, Alberta, Canada
Hello,

Updating can be slow Because the screen is also being updated while Excel is writing to cells.

Try and add
excel.Application.Screenupdating = false
before running the update, and add
excel.Application.Screenupdating = true
at the end of your update.

You may also want to minimize the workbook while updating. Post this at the beginning
excel.ActiveWindow.WindowState = office.xlMinimized
and this at the end of your code
excel.ActiveWindow.WindowState = office.xlMaximized


Could you rerun your code with this modification and let me know if this helps?

Sincerely,

Eugene


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Tue May 29, 2012 12:27 am 
Offline

Joined: Mon May 30, 2011 12:56 am
Posts: 702
A thought:
Is chr(10) enough?
It may need Chr(10) chr(13) to recognise the need for new cells.

(windows vs unix line endings)


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Tue May 29, 2012 8:11 am 
Offline

Joined: Tue Oct 02, 2007 6:47 am
Posts: 32
Eugene, screenupdating = false does not help, however xlminimized does help. I am seeing a 40% increase in speed.

kermit, I have tried all combinations of chr(10) and chr(13). Maybe the issue is the way I address the cell? I would assume I could just write to A1 and let the line break spill over to A2, etc. but that's not the case.

When I do use chr(10) and write just to A1, the speed is very fast. It's just all the data is in A1 as opposed to A1:A16000

Any other ideas? Thanks for all of the responses.
Sean


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Tue May 29, 2012 10:12 pm 
Offline

Joined: Mon Jan 02, 2006 11:43 am
Posts: 172
Location: Calgary, Alberta, Canada
Hello Sean,

Thanks for the feedback. I slightly modified your code, and I found that this version seems to work:

dim excel as new ExcelApplication
dim i as integer
excel.Visible = true
excel.Workbooks.Add

for i = 0 to 16000
excel.Range("A" +CStr(i+1)).value = CStr(i)
next

MsgBox "done"
exception err
MsgBox err.message


I am not sure what your entire line of code looks like, whereas this example populates 16,000 cells in about 17 seconds.

Just in case your curious for some other helpful examples, I wrote the book on REALStudio for Excel in which the Index and Table of Contents can be viewed at: http://www.scispec.ca/REALbasic/Publish/Publish.htm

I hope this helps. :)

Eugene
Windows 7, REALStudio 2011 R4.3


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Wed May 30, 2012 1:28 am 
Offline

Joined: Mon May 30, 2011 12:56 am
Posts: 702
Works using the clipboard..
Assembling the text takes time, but getting it into Excel.. negligible.

dim excel as new ExcelApplication
dim w as ExcelWorkbook
dim i as integer
dim s as string
dim r as ExcelRange

'generate our text
for i = 1 to 16000
s = s + "A" + cstr(i) + "B" + EndOfLine.Windows
next

'copy to clipboard
dim c as new Clipboard
c.SetText(s)
c.Close

excel.Visible = true
w =excel.Workbooks.Add
r = excel.Range("A1:A16000")
r.Select_
excel.ActiveSheet.PasteSpecial("Text",false,false)

MsgBox "done"
exception err
MsgBox err.message


Top
 Profile  
Reply with quote  
 Post subject: Re: Move Array Data to Excel
PostPosted: Wed May 30, 2012 8:44 pm 
Offline

Joined: Tue Oct 02, 2007 6:47 am
Posts: 32
Eugene, this didn't really help the speed much. Thanks for your efforts- I've learned some new tricks.

Kermit, the clipboard method works and I can get the time down to about a second or so. I think using the ExcelRange variable and the EndofLine.Windows convention were key to the problem.

Thanks, Sean


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group