Solved

Show counter/dashboard during Automation

Posted on 2011-02-28
10
541 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
Independent Software Vendors: 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 85
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 85
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

632 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