Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
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 "JT1977" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
On 8 avr, 22:28, JT1977 wrote:
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? 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. < |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
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 "ker_01" wrote: 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 "JT1977" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
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" wrote: 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 "JT1977" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Delayed Response to MsgBox
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 "JT1977" wrote: 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" wrote: 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 "JT1977" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to add a textbox if user chooses yes in response to msgbox. | Excel Programming | |||
delayed response to any command | New Users to Excel | |||
Msgbox delayed using AFTERUPDATE | Excel Programming | |||
Msgbox delayed using AFTERUPDATE | Excel Programming | |||
Default Response to a MsgBox | Excel Programming |