Solved

Counter during Automation- Stopping the process

Posted on 2011-03-21
23
242 Views
Last Modified: 2013-11-27
Continuing my question http:Q_26852019.html
Can we send an event?
I want to be able to stop the counter from the access form either using a mouse click or pressing a button.
It seems that Excel has full control & Doevents etc doesn't give me access to the form.
0
Comment
Question by:tbsgadi
  • 13
  • 10
23 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35182370
It's beginning to sound like you should probably rethink this approach, but it can be done using something like this:
In the ThisWorkbook module of your workbook:
Option Explicit

Public objTB As Object

Open in new window


Then the XL macro becomes for example:
Public Sub TestRoutine()
   Dim n As Long
   Do
      n = n + 1
      With ThisWorkbook.objTB
         .Text = "Processing item number " & n
         .Parent.repaint
         DoEvents
      End With
   Loop While ThisWorkbook.objTB.Tag <> "Stop" And n < 100000
End Sub

Open in new window


and the Access code is:
Private Sub Command0_Click()
   Dim objXL As Object, objWB As Object
   Set objXL = GetObject(, "Excel.Application")
   Set objWB = objXL.workbooks.Open("C:\forums\ee\xlcallbacktest.xls")
   Set objWB.objtb = Me.Text3
   objXL.Visible = False
   Me.Text3.SetFocus
   objXL.Ontime Now(), "TestRoutine"
   objXL.Visible = True
End Sub

Private Sub Command5_Click()
   Me.Text3.Tag = "Stop"
End Sub

Open in new window


0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35187757
at  
Set objWB.objtb = Me.Text3
I get Error 438 (Object doesn't support this property or method)

I've added Public objTB As Object to  the ThisWorkbook module
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187781
Me.Text3 is whatever the textbox is on your form
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 46

Author Comment

by:tbsgadi
ID: 35187782
I know
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35187797
Maybe I need early binding?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187802
How did you declare objWB in the Access code?
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35187805
Same as you-as Object
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187812
Then are you absolutely sure you put the 'Public objTB As Object' line in the right place in the workbook (and saved the workbook?)
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35187816
Absolutely!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187856
Can you post the workbook you are testing on and the exact Access code you have?
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35187936
I'll see if I can manage to dissect it not to give away any trade secrets!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35187948
You can also test the attached db and workbook which I was testing on - you'll need to alter or create the path to the workbook as specified in the code.
EE.zip
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35188047
Could it be that I'm using

Set objXL = New excel.Application ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35188078
That should not be a problem. Does my sample db work for you?
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35188105
It works but I'm getting an application error on     .Parent.repaint
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35188218
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35188416
What error? It works fine for me (Office 2003)
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35189301
Maybe Windows 7?

<<It's beginning to sound like you should probably rethink this approach>>
How else would you do it?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35194096
I would probably pass control over to Excel and display a progress meter there. Having said that, I can't see why this isn't working for you.
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35198165
I notice another big difference between our examples.
In yours you can click on the access form or on a text box while the counter is running. In mine the form is frozen & although the counter works I can't select anything. If I could click anything I wouldn't have any problem.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35198183
Are you using OnTime to launch the macro?
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35198190
yup
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 35205017
I found one of the problems.
I was declaring objXL as Excel.Application When I declare it as Object I don't get the error
Error 438 (Object doesn't support this property or method)
Why would that be?

However my form is still frozen & I can't press any of the buttons.
It's open as popup Dialog, I've tried other configurations...any other ideas?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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