Solved

Show counter/dashboard during Automation

Posted on 2011-02-28
10
529 Views
Last Modified: 2013-11-27
I have a form in access that runs automation in excel.
The Excel application iterates through code many hundreds of times. I don't know how many untill it finishes.
I'd like to display a counter of sorts in the access form.
What's the best approach?
Would it slow down the application substantially?


Thanks

Gary
0
Comment
Question by:tbsgadi
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34995745
Hi,

I use application.statusbar = "Progresscounter = " & i        where i should be your counter.
When the itteration has ended I put

application.statusbar = "ready"

kr

Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34995754
Oops did not see your access ... will have to re-think..
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34995805
This is what I came up with..
You can not share variables between excel macro and access macro, but you can use excel cells for the interchange..


Let excel write to a cell the counter , if multiple counters use the one changing the slowest.
then let your access look at this cell.. and present it to your form.


Kind regards

Eric
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 84
ID: 34996272
If you don't know how many times you'll have to loop through the code, then you really can't use a ProgressBar. That requires you to know the Min value (normally 1 or 0), the Max value and the Current value.

Otherwise, you could just use the Status bar to let the user know that something is still going on, depending on exactly how you're automating Excel. Can you show where/how you do this?
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 34996374
Yup I know I don't want a progress bar, just some form of counter so the user knows it's still working & not just showing an hour glass.

My code is very long but the relevant parts in Access

Set objExcel = New Excel.Application

With objExcel
     .Application.Run ("RunOptionPricing")


In Excel I have a for next & a while - wend

While Range("TestShares") > 0
 i = i + 1
....


How would I show the i from excel in access?



0
 
LVL 84
ID: 34996400
Hmmm ... I'm not sure you could, since the RunOptionPricing is managed from within Excel. The While - Wend loop would work IF you were doing this from Excel, but AFAIK, the automatED object (Excel, in this case) cannot communicate back to the automatING object (Access).

You might be able to kludge this in some manner, like writing out a "current" value to a Text file, and have Access read that Text file. I'm not sure how well that would translate over.

Or move the RunOptionPricing routine to the Access environment, where you'd have full control over it. Not sure if that's even doable, of course.

And finally, if the Excel application is visible during this process, you can do as suggested by Zwiekhorst and provide feedback on the Worksheet.
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 34996422
Thanks, that's what i thought.
I'll wait a bit to see if anybody else has any ideas.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34996710
Temp = SysCmd(acSysCmdSetStatus, i)

should display the status in Access statusbar

objExcel.statusbar=i

should display the status in Excel statusbar

Saqib
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34996732
You need to have your Excel routine call back directly to the calling application - for example:
Access form code:
Dim objXL As Object
Set objXL = GetObject("C:\Forums\EE\XLCallbackTest.xls")
Me.Text2.SetFocus
objXL.Application.Run "'XLCallbackTest.xls'!TestRoutine", Me.Text2

Open in new window


Excel code:
Public Sub TestRoutine(objTB As Object)
   Dim n As Long
   For n = 1 To 100
      objTB.Text = "Processing item number " & n
      objTB.Parent.repaint
     ' your processing code goes here
      DoEvents
   Next n
End Sub

Open in new window

0
 
LVL 46

Author Closing Comment

by:tbsgadi
ID: 34996854
Awesome!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question