Time Delayed Response to MsgBox

[JT1977]

I have a several "research" files that I set up to run a macro as soon as a
file is opened. In each case the macro retrieves and summarizes a bunch of
data taking several minutes to execute. I'm setting up a "master" file with
a simple macro that will open and close each of the "research" files
sequentially so I can run the series of reports without being at the computer
for hours.

There are times where I want to open a "research" file to review the data
but not execute the macro. I'd like to add a MsgBox that prompts the user to
by-pass the macro. However, if I do this when I use the "master" file to
open and run the series of "research" files it will just wait for a response
to the MsgBox before proceeding and thus will once again be dependent on
human intervention.

Is there a way to setup the MsgBox so that if it waits for X amount of time
without receiving a response from the user a default value for the response
will be used which will continue running the macro?

 

[ker_01]

Here is an alternative approach that may be a little safer than techniques
like 'sendkeys' to close out your msgboxes; just make the appearance of those
msgboxes contingent on how your file was opened. Here is an example:

In a vbs file (which is in my startup folder, and also a task in windows
scheduler for a 1:00 am run:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\path\file.xls")
objExcel.Visible = True
objExcel.Run "CrunchIt", "SkipEmailPrompt"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
end if

Sub CrunchIt(Optional EmailAlert As String)

StartTime = Now()

If Sheet19.Range("A2").Value <> Date Then
If EmailAlert <> "SkipEmailPrompt" Then
MsgBox "The raw data has not been refreshed yet today; please be
patient while the raw data is refreshed prior to crunching the data",
vbOKOnly, "Current Data Not Found"
End If
'rest of the code

So by passing the string "SkipEmailPrompt" from the vbs (or from another
workbook, or however you want to call it) you can include or bypass the
sections of code that require user intervention. When this macro is run
without passing along that string, the msgbox is always shown.

HTH,
Keith

 

[new1]

I have a several "research" files that I set up to run a macro as soon asa
file is opened.  In each case the macro retrieves and summarizes a bunch of
data taking several minutes to execute.  I'm setting up a "master" filewith
a simple macro that will open and close each of the "research" files
sequentially so I can run the series of reports without being at the computer
for hours.

There are times where I want to open a "research" file to review the data
but not execute the macro.  I'd like to add a MsgBox that prompts the user to
by-pass the macro.  However, if I do this when I use the "master" file to
open and run the series of "research" files it will just wait for a response
to the MsgBox before proceeding and thus will once again be dependent on
human intervention.

Is there a way to setup the MsgBox so that if it waits for X amount of time
without receiving a response from the user a default value for the response
will be used which will continue running the macro?

Hello,

I would suggest trying to use the VBA Wait function to measure the X
amount of time, and when the time is expired send a simulated "OK"
key stroke (with VBA SendKeys function ).

Hope this help.

new1@[no/spam]realce.net <

 

[JT1977]

Thanks for your help ker_01. This works great.

This did answer my question but I'm a little more of a novice than what you
assumed. After working through this I got it to work. I added some comments
to ker_01's notes to clarify what I did not understand at first

'Place this code in the macro of the master file you are using to open
secondary files
Set objExcel = CreateObject("Excel.Application")
'Opens the secondary file
Set objWorkbook = objExcel.Workbooks.Open("C:\path\file.xls")
objExcel.Visible = True
' "CrunchIt" is a subroutine in the secondary file. The below statement
initiates the running of this macro
' This next statement passes "SkipEmailPrompt" as the value of an optional
variable
' into the macro of the secondary file. Excel recognizes it as the value for
' this variable because of this statement
objExcel.Run "CrunchIt", "SkipEmailPrompt"

'This subroutine is a macro in the secondary file
'Because the CrunchIt and EmailAlert are in the name of the subroutine it
'recognizes the info from the line above.
Sub CrunchIt(Optional EmailAlert As String)

'Variable used to skip the MsgBox
If EmailAlert <> "SkipEmailPrompt" Then
MsgBox "The raw data has not been refreshed yet today; please be
patient while the raw data is refreshed prior to crunching the data",
vbOKOnly, "Current Data Not Found"
End If

 

[JT1977]

ker_01,

I have the primary vbs file opening and bypassing the code of the secondary
vbs file just fine now. How do I end the code of the secondary file so it
closes the secondary file and returns control to the next step of the primary
file code? Right now the code of the secondary code ends but then the code
in the primary file errors out (see below for the code I'm using in the
primary file).

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("http://www1....)
objExcel.Visible = True
objExcel.Run "Excel_Starts_Here", "SkipUserConfirmation"
ERRORS OUT HERE
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close (0)
objExcel.Quit

 

[ker_01]

When it is working properly, control should return to your calling sub
automatically (at least in the situations I've used it in). It would act no
differently than if you had one sub calling another sub in the same workbook.
To verify this, I just created two workbooks (Excel 2003); one is called
GTest1 and the other is called GTest2. Both are saved on my desktop.

In GTest1 I placed the following code:

Sub CrunchIt(Optional MyParameter As String)
If MyParameter = "Y" Then
MsgBox "Msgbox 1 is working", vbOKOnly, "Halfway there"
End If
End Sub

and in GTest2 I placed this code:

Sub MainWorkbook()

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and
Settings\ker_01\Desktop\GTest1.xls")
objExcel.Visible = True
objExcel.Run "CrunchIt", "Y"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close (0)
objExcel.Quit

MsgBox "Msgbox 2 is working", vbOKOnly, "All done"

End Sub

When I change the line [objExcel.Run "CrunchIt", "Y"] from a "Y" to a "N"
and back, I can trigger the msgbox #1 on demand. Regardless of that, I always
get the msgbox 2 alert that confirms control returned to the calling sub.

So, since it isn't working in your application, let's troubleshoot;

Potential issue #1: I see you aren't opening a file using a network
filepath, but instead are using a URL (www.etc..). If you need to save back
to a webserver, you probably need to use FTP or some alternative to a direct
save. Under normal conditions when you run your macro today (and have the
msgbox pop up under all circumstances) do you save the file, and if so, via
what process? If it is already saved in your code, you can eliminate the save
line here [objExcel.ActiveWorkbook.Save]. Or, if you don't need to save the
file, eliminate the line. If you /do/ need to save the file, consider
changing that line to

objExcel.ActiveWorkbook.SaveAs Filename:= "C:\path\filename.xls" (whatever
path & name is safe)

note that this will just save the file, and I can't recall the syntax off
the top of my head to force it to overwrite a previous file with the same
name without popping up a confirmation box, so you could just turn off alerts:

application.displayalerts= false
objExcel.ActiveWorkbook.SaveAs Filename:= "C:\path\filename.xls" (whatever
path is safe)
application.displayalerts = true

Or you could just ensure that a different filename will be used each time it
is saved. This assumes you wouldn't run this more than once per day
(otherwise you would add hours/mins/secs for additional precision to maintain
unique filenames):

TempDTString = year(now()) & format(month(now()),"00") & _
format(day(now()),"00")

objExcel.ActiveWorkbook.SaveAs Filename:= "C:\path\filename" & " " &
TempDTString & ".xls"


If the save thing isn't it, what version of Excel are you using? Are you
accessing a file on a sharepoint site, or a regular URL?

If that doesn't help get you up and running, post back and we can maybe look
at more of your code, discuss how to set breakpoints to walk through your
code, etc.

Best,
Keith