Solved

Show counter/dashboard during Automation

Posted on 2011-02-28
10
500 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now