Replies

Geoff

I re-coded in an Array(8), vice 8 individual variable and found a slight (minimal) speed improvement. Not much of one, but enough to give me hope.

I was wrong about not having any trouble with the array. It took a fair amount of tinkering to get the darn thing to do what I wanted!

I was all set to re-code again, this time using 8 Array(8, x) that I would redim as x increased, to then create each of the 8 sheets from their respective arrays all at once… but someone else pointed me towards using Excels database objects instead.

I recorded a macro of Excel doing the “Get External Data” text import (the results are not pretty, but it is VERY fast) and discovered that it uses “QueryTables.Add”. I think I’ll go bark up that tree for a while… and see if it gets me anywhere.

Thanks for your help… and be prepared… I may very well end up back using the large multi-dimensional array approach… in which case I will probably be asking for help again!

Either way, I will keep you posted.

David

I stole your signature…

Very slick! (and professional)

Thanks for sharing, it’s given me a great idea for my own version.

David

Here are some code snippets I use…

Setting calculations to manual will keep Excel from updating any “dependant” cells until after your vba code is finished running… this may or may not have a huge effect on the speed of your vba code.

‘ Set Calculations to Manual
‘ Create a variable to save the user preference setting
Dim saveCalculation As String
‘ Save the users setting for the xlCalculation
saveCalculation = Application.Calculation
‘ Turn off calculations… speeds up VBA code
Application.Calculation = xlCalculationManual
‘ When done… reset back to user preferrence
Application.Calculation = saveCalculation

Turning off screen updating sounds like exactly what you were asking about. Be aware though, ALL Excel screen updates are disabled by the following code, which can make your application appear to freeze. I always use some other activitiy indicator in conjunction with disabling screen updates… like setting a message in the status bar to “Please wait…” and, for long routines, updating the status bar message every so often throughout my code.

‘ Set StatusBar On & send a message
‘ Create a variable to save the user preference setting
Dim saveStatusBar As String
‘ Save the users setting for the StatusBar
saveStatusBar = Application.DisplayStatusBar
‘ Turn on the StatusBar
Application.DisplayStatusBar = True
Application.StatusBar = “This will take some time. Please be patient…”

‘ Turn off screen updates… makes Excel appear to freeze!!!
Application.ScreenUpdating = False

”” Run your time consuming routine here

‘ Turn screen updates back on
Application.ScreenUpdating = True

‘ Send another message
Application.StatusBar = “Processing complete. Thank you for your patience…”

‘ Set the StatusBar back to the user preferrence
Application.DisplayStatusBar = saveStatusBar

BTW… love your signature! (a fellow Canadian)

Hope this helps…

Geoff

Thank you. That

Geoff

Your statement: “then at the end moving that array into the worksheet really speeded things up” really caught my attention.

I am working on something similar (importing ~ 50,000 lines of csv values into an excel workbook) and would love to see an example of the code you used for your “move-array” function. I am confident I can build the array without problems but have not found any examples of how to then move the array into a row of cells. I currently place each value individually… and yes, it is SLOW!

Thanks Andrew (and Wassim)

As usual, a simple post to The Lounge made everything clear.

Thanks especially for the link to the other thread… a quick and effective method of providing me more information… and very applicable too.

I apologise for taking so long to post my response here

[indent]

---

Now, does anyone know why Linux distros use a Penguin?

---

[/indent]

It’s sad but true… the trusty penguin (the bird that can’t fly) seems to have come to stand for “Linux… it won’t fly on the desktop”!

If all you want is to “just display a csv or tab delimited file” why not try List.

What does list do? Its a simple database. Modeled after the address book, it should be intuitive to use, its supposed to look just like every other pilot application. If you just want to use list on your Pilot you can stop reading here, go use it

List is compatible with ALL versions of PalmOS. (PalmOS1.0 -> PalmOS3.5)

Example input file:
===cut===
databasename,customfield1,customfield2,category1,category2
category1,field1data,field2data,notedata
===cut===

URL: http://www.quux.net/list/download/listfull.zip
Page: http://www.magma.ca/~roo/list/list.html

From what I have seen so far (I just found List myself) it seems to be a simple, easy to use solution for handling simple lists (go figure ).

Hope this helps…