Need some help making this macro a more reliable solution

[Fitz1000]

New to creating macros and have found this resource to be most helpful! I have created the macro below (with some help) and it works....about 95% of the time. Unfortunately, this is not reliable enough. The macro is supposed to check to see if a particular spreadsheet or a particular database is open and if it is, quit (which it does famously). If neither are open, it is supposed to open a series of excel spreadsheets that are configured to refresh automatically from a database. This works most of the time, but periodically I get the message asking me to click "Update" if I want to update the data. I have the statement Application.AskToUpdateLinks = False in the macro that usually seems to take care of this, but for some reason, on occasion it does not. This macro runs every 15 minutes and if it gets stuck once, the rest failor require some kind of manual intervention. If anyone has any pointers on my macro below (related to this issue or otherwise), I would be happy to hear them.

VBSTART
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.
Function IsFileOpen(ByVal sFileName)
Const ForAppending = 8
Set oFSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next ' Turn error checking off.
' Attempt to open the file and lock it.
Set f = oFSO.OpenTextFile(sFileName, ForAppending, False)
iErrNum = Err.Number ' Save the error number that occurred.
f.Close ' Close the file.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case iErrNum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Err.Raise iErrNum
End Select
End Function
VBEND

VBEval>IsFileOpen("G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Chat Requests Received.xls"),IsItOpen
If>IsItOpen=False
VBEval>IsFileOpen("G:\MemberCare\Managers\MC Analytics Data\MC Analytics Portal.mdb"),IsItOpen
If>IsItOpen=False

Let>filename=G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Chat Requests Received.xls
IfFileExists>filename

Application.AskToUpdateLinks = False
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

Else
MessageModal>Could not find: G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Chat Requests Received.xls
Endif




Let>filename=G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Chat Sessions Accepted.xls
IfFileExists>filename

Application.AskToUpdateLinks = False
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

Else
MessageModal>Could not find: G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Chat Sessions Accepted.xls
Endif


Let>filename=G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Message Inflow.xls
IfFileExists>filename

Application.AskToUpdateLinks = False
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

Else
MessageModal>Could not find: G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Message Inflow.xls
Endif


Let>filename=G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Reply Outflow.xls
IfFileExists>filename

Application.AskToUpdateLinks = False
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

Else
MessageModal>Could not find: G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Reply Outflow.xls
Endif


Let>filename=G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Reports.xls
IfFileExists>filename

ExecuteFile>filename
Press CTRL
Send>q
Wait>1
//cntrl-q runs another macro called tabdelimitedsave that copies some of the data from one sheet to another and then saves the files

Else
MessageModal>Could not find: G:\MemberCare\Managers\MC Analytics Data\WFM Data\WFM Data - Reports.xls
Endif
Endif
Endif

[JRL]

Your script looks like it should work although I can't test it. It would seem to me that the error message

asking me to click "Update"

Is probably coming from Excel and not from Macro Scheduler. you might try running another macro concurrently with this one that just sits and waits for the error message to pop up, then takes whatever steps are required to deal with it. Something like:

Code: Select all

Label>start
WaitWindowOpen>Error Message*
Wait>0.5
Press Enter
Goto>start

Hope this helps,
Dick

[pgriffin]

This is cut/pasted from Excel Help.

This option is for the current user only, and affects every workbook opened. Other users of the workbook are not affected. This option also affects links to other programs.

On the Tools menu, click Options, and then click the Edit tab.
Clear the Ask to update automatic links check box. If the box is cleared, the links are automatically updated, and no prompt is displayed.

[Fitz1000]

Thanks, I have edited the Ask To Update feature and we'll see what happens. If that does not take care of it, I will try the dual macros option.

Thanks again!