Trying again for an answer.

S

Sarah

I have macros running in excel which massage data created by Live Office
(Business Objects Queries.) Every time a macro tries to edit the data
returned by the query I am hounded by a message which says, "The value(s)that
came from a BusinessObjects Enterprise data source has just been
edited/deleted. Do you want to proceed with the change?".

"With most popup messages I just use the displayalerts statement to get
around it. That does not work for this message which specifically addresses
the Live Office edits. Does anyone know how to suppress it. I have
discovered that if the file created by the query is saved as an older version
of excel and then resaved it as the new version, the messages do not appear,
however it is a bit embarassing to have to ask a user to remember to execute
such a cluge.
 
J

JLGWhiz

It is not a VBA generated message. You would need to know the specifics of
the source error criteria to know how to avoid it.
 
J

Jef Gorbach

I have macros running in excel which massage data created by Live Office
(Business Objects Queries.)  Every time a macro tries to edit the data
returned by the query I am hounded by a message which says, "The value(s)that
came from a BusinessObjects Enterprise data source has just been
edited/deleted.  Do you want to proceed with the change?".

"With most popup messages I just use the displayalerts statement to get
around it.  That does not work for this message which specifically addresses
the Live Office edits.  Does anyone know how to suppress it.  I have
discovered that if the file created by the query is saved as an older version
of excel and then resaved it as the new version, the messages do not appear,
however it is a bit embarassing to have to ask a user to remember to execute
such a cluge.

Unfamiliar with Live Office, but the message sounds like that
application is somehow still linked/aware of the query so you need to
either look towards suppressing the message from that end, or since
saving the results and loading the resultant file instead seems to
work, consider implementing it within your routine.
 
T

Tom Hutchins

You might try this. I had to use something similar to clear a pesky popup
from Hyperion SmartView.

If the messagebox always has the same title (or not always the same, but
something you can predict), you could use AppActivate to check if it exists.
In conjunction with OnTime and SendKeys, you could frequently try to activate
that window (messagebox). If it can activate it, then use SendKeys to send
Escape (or Enter or ??) to close it.

Here is a simple example. Copy the following VBA code into a module in
Excel. Launch MS Word and select File >> Save As (or Office button >> Save
As).

Switch back to Excel and run the ClosePopup1 macro. As long as cell A1 on
Sheet1 is empty, it will try to activate the Save As window in Word every 5
seconds. If it succeeds, it sends Enter, which closes the Save As dialog. If
it fails (because there is no Save As dialog present), it sends no keystrokes
and starts another 5-second countdown. If you select File >> Save As in Word,
within 5 seconds the macro will close it.

Public RunWhen As Double
Public RunWhat As String
Const PopupName = "Save As"

Public Sub ClosePopup1()
'Call ClosePopup2 5 seconds later.
RunWhen = Now + TimeValue("00:00:05")
RunWhat = "ClosePopup2"
Application.OnTime RunWhen, RunWhat
End Sub

Private Sub ClosePopup2()
'Try to switch to the application with a "Save As" window open.
On Error GoTo DoMore
AppActivate PopupName
'If we were able to activate the desired app, call ClosePopup3
'one second from now.
RunWhen = Now + TimeValue("00:00:01")
RunWhat = "ClosePopup3"
Application.OnTime RunWhen, RunWhat
DoMore:
DoEvents
'Quit when anything is entered in A1 on Sheet1 of this workbook.
If Len(ThisWorkbook.Sheets("Sheet1").Range("A1").Value) = 0 Then
'Otherwise, call ClosePopup1 to start the process over again.
Call ClosePopup1
End If
End Sub

Private Sub ClosePopup3()
'Send an Escape keystroke.
Application.SendKeys "{Esc}"
'kill any scheduled timer events
Application.OnTime RunWhen, RunWhat, False
End Sub

Hope this helps,

Hutch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top