tbsgadi
asked on
Show counter/dashboard during Automation
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
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
Oops did not see your access ... will have to re-think..
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
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
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?
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?
ASKER
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?
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?
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.
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.
ASKER
Thanks, that's what i thought.
I'll wait a bit to see if anybody else has any ideas.
I'll wait a bit to see if anybody else has any ideas.
Temp = SysCmd(acSysCmdSetStatus, i)
should display the status in Access statusbar
objExcel.statusbar=i
should display the status in Excel statusbar
Saqib
should display the status in Access statusbar
objExcel.statusbar=i
should display the status in Excel statusbar
Saqib
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome!
I use application.statusbar = "Progresscounter = " & i where i should be your counter.
When the itteration has ended I put
application.statusbar = "ready"
kr
Eric