Solved

Show counter/dashboard during Automation

Posted on 2011-02-28
10
535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

751 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