VB command to cancel/esc when prompt box appears

gifer

New Member
Joined
Nov 26, 2005
Messages
8
I have a file book1 that I am linking to book2 file. These files are on different PCs on a network. When a macro runs in book2 causing calculations to complete, I end the macro with ActiveWorkbook.Save. (It’s a long story, but I have to save book2 in order for the Edit/links... 'Update now', in book 1 to work).

In book1, I have a macro run 'Update now', every 5 minutes. That way, if the remote book, book2 has saved changes, book1 will get updated. However, if book2 happens to be performing a File/Save at the same time, book1 can't find the file and opens the File/Open window, so the user can manually look for the file. Since this macro runs on a unmanned PC, the entire operation stops and waits.

Since the two files are on different PCs, I can't sync the macros or write it all in one macro to avoid the conflict. I was wondering if there is code that can recognize when the File/Open window appears, and just cancel or esc out. Then loop back and run the 'Update now' command line again. The remote book2 only takes a couple seconds to save, so I would write delay code before looping back. I tried On Error Resume Next, but it really isn't an error, so that didn't work. :confused:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
since that dialogue box only comes up if the file is not found then you can use code to check to see if file exist and only if it exists, does it run.

example:

Code:
if dir("c:\test.txt") = "" then
Exit Sub
else
....
end if

The "" will only return if the file is not found.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top