.Find method slow in Excel when not topmost window?

[Nikita6003]

Hi all,

I have a funny little problem with one of the Excel tools we wrote to extract & report dataq from a Sybase (system 11) database. It's not a majow issue at the moment, but might become so in the not-too-distant future, so any thoughts/solutions welcome ... !

The tool has two types of reporting: a summary showing totals per currency, and a detail section showing the totals per currency per time period.

The summary looks like this:
USD EUR GBP AUD ...
Place1 1234 8712 3210 5674
Place2 6543 1113 789 344
...
etc

and is created in a rather roundabout way. The tool used to be based on an Access database, and we used the Access crosstab query to generate these summary pages - easy & quick.
However, TRANSFORM not being a standard SQL statement, we were a bit stuck when the move to Sybase was proposed. So we found a solution (a bit of a fudge!) We're using Excel's crosstab wizard & passing it a connect string to sybase & the execute command for the appropriate Sybase stored procedure. The crosstab generated includes ALL data for the reports we're generating (65 in total), but not all data is used in all reports. We then wrote a sub using Excel's FIND method to find the totals we're looking for & reporting the result on a separate report sheet.

Now here's the problem: when Excel's the topmost window this works fine, and is very fast (although not optimal! but unfortunately we've got a code-freeze for the next few months). When Excel is NOT the topmost window, the report grinds on very sloooowly (timings for a 3MB report are 13 minutes when Excel is topmost, up to one and a half hours when it isn't - I kid you not!)

Has anyone heard of this phenomenon?!?
It's not an issue now because there is a spare PC on which we run the reports, BUT when the new guy arrives he'll have to use this PC so there's no spares left.

Your thoughts, please! ;-)

Cheers & thanks
Nikki

 

[rajja]

Hi,
i am new 2 this tek tips.
i am not sure how this works. i don't no how i enede up HERE. maybe because my girlfriends name is Nikita ,(nikki).
n e ways can u tell me how do i aks a question? i a mjust a bigginer in ACESS and i need sum help, but i don't no how to ask.

 

[Nikita6003]

Hi rajja

just go to the "Forum List"link and click on one of the Access forums (you best bet is probably the Access forms, Access VBA or Access OTHER forum). You can also click on my name & select one of the 4 Access forums that I subscribe to. The forum you select will automatically be added to your own personal forums list (which is on the left hand side of the screen). Then all you do is type your question in the text box below the list of questions already ask & yours will be added to the list ;-)

Hope this helps - regards to my namesake ...

Cheers
Nikki

PS - read this thru' first ...

How To Post A "Good" Question
Look Before You Leap: Use the FAQ page and the Search function before posting a question. Somebody may have asked and answered your question yesterday.



Think! Carefully word the title of the thread to reflect the problem. This makes it more likely to attract the attention of those who actually know the answer. It will also allow others in the future to find your question (and answer) by using the search engine.



Edit Rather Than Braindump: We're all busy people - please be respectful by taking your time to briefly specify the problem, question, and result you are trying to achieve. Include all details that might be helpful, such as background that may affect the problem/solution, solutions you've already tried that haven't worked (and why). Leave out any details or chat that won't help solve the problem, and address only one question/problem per post.



Programming Code: If including programming code include any exact error screens and only the smallest possible code sample that will illustrate your problem. Also, be sure to surround your programming code with the

tags. This tag will format your code in a monospace font as well as avoid possible conflicts with other HTML output.



Proofread: In terms of speed for those scanning many threads, spelling and grammar count! Make it easy for others by proofing your posts for typos, spelling, grammar, as well as clarity.



Follow Up: Be sure to mark the "notify me" box when you post. The system will send you a note if someone replies. We suggest you don't post your e-mail address as it only attracts sales people and recruiters to the forums.



Close


- Special thanks to member Elizabeth
and all the Round Table members
who contributed to this helpful tip.

 

[BrainB]

Seeing as this one has popped up ........
FWIW you can save a lot of FIND time by limiting the search to a single row or column (assuming this is possible) rather than having to search the whole sheet eg

MySheet.Columns(1).Find .............. Regards
BrianB
** Let us know if you get something that works !
================================

 

[Nikita6003]

Hi Brian,

thanks for that - but I need to a search across the row *and* column of a crosstab (it's entities againt currency exposure, and I need to generate a quick summary for each of the 60-odd reports without having to go to the Sybase DB all the time - that *really* slows things down ;-)). The search is limited against the crosstab rather than the whole sheet.
The issue we've been having is that the Find is slow when xl isn't running as topmost window, while it's lightning fast when it is.

Just another MS quirk, I guess.

Anyway, since I've now moved to another department, the PC to run these reports on is still free & is now dedicated to running xl report tool as topmost window


Thanks
Nikki