Excel File Sharing

[IceBox]

OK, I'm pulling my hair out, so I turn to the forum for help...

 

I have created a macro to run daily. It opens an Excel file each day and does some work in it. If somebody has that file open at the time the macro runs, it would be a problem, because the "File in Use" box would appear. I would like to program the macro to adapt to this. I would like it to open the file, wait 10 seconds (10 seconds is overkill, but this macro runs on days when I am not in the office, so there is no rush, and it's better to be safe than sorry), and then check to see if the "File In Use" pop-up is present. If it is, I would like it to press "N" for "Notify", and then wait indefinitely for the "File Now Available" box to appear and press "W" for Read-Write, and then continue on with what it has to do. This appears to be very simple, but I have tried 3 ways to do this, and none of them work:

 

1. I captured both pop-ups as Controls (The File in Use and the File Now Available Pop-up), and used

 

If Control %C5% Visible
Text Type: n
etc etc

 

2&3. I used two different Window Title Commands:

 

If Window Title "File in Use" is on top
Text Type: n
etc etc

AND

If Window Title "File in Use" is running
Text Type: n
etc etc

 

None of these methods worked. It almost seems like the "File in Use" box freezes Macro Express completely, and will not recognize ANY MacExp commands until that box goes away. You can't even abort the macro while that box is displaying, either with SCRL LK+Pause or by right clicking the "Runner" in the system tray. If you try to abort, once you get rid of the pop-up box, it actually does abort, but it will not do it while the box is there.

 

It is very rare that this file will be open when the macro runs, but if it is, my PC will remain stuck in that spot for the rest of the day, and all my other daily scheduled macros will not run.

 

Anybody ever run into this problem? Have any ideas? Is this a bug that can be fixed?

[m204prgmr]

Wow...

Have you tried Files/Folders - If File Ready / If File Not Ready.

 

These commands test to see if the file you specify is or is not ready to accept input. It tests to see if the file is still busy or not.

 

Robert

hope this helps just a little

[kevin]

Icebox,

 

There are some dialogs that 'freeze' macros. Our research indicates that when certain dialogs are active, Windows is not letting Macro Express run. I also suggest trying the "If File Ready", "If File Not Ready" or the "Wait File Ready" commands.