Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Show counter/dashboard during Automation

Posted on 2011-02-28
10
Medium Priority
?
543 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

722 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