Automating accepting "OK" from msgbox in another file

[Steveapa]

Hi All. I am doing some automation for users of an IT dept created file. The IT file is a protected workbook with protected modules.

Current process: The user opens the IT xlsm file, copies / Pastes by value the updated data (from USER file) and then saves the IT file (located on a sharepoint). The IT file also has a macro button to "Upload" the new raw data to a server. So, 2 things are done, the IT file is updated and saved, and Data is push to a server. Regardless if you hit the macro button to upload the data to the server or not, the IT Workbook_BeforeSave has a MsgBox("Do you want to upload data", vbYesNo, strTitle), you need to click yes or no.

I am automating this from the USER file. From a macro button in the USER file, I
1) open the IT (sharepoint) file
2) auto copy paste special data from USER to IT
3) save IT file
4) I still need to upload the data in the file to their server....

So I can either do a or b...
a) On Save, the IT file Workbook_BeforeSave runs asking if you want to upload data Yes / No. How can I accept the "yes" with code from the USER file...
or
b) run IT upload macro "Public Sub btnUpload_Click()" from USER and bypass the BeforeSave Msgbox


Thanks
Steve

[ByteMarks]

Code execution is paused when a msgbox is shown so I don't think you can click it programmatically.
You probably need some flag or variable in the IT file which you can set from the user file, or maybe something in the IT file that detects that the active workbook is the user fileto skip the msgbox altogether

[Steveapa]

Hey... Thanks for the Reply. Yes, the solution is in the IT file. But as an engineer / user / self taught for 30yrs... upgrading their solution to make it easier for the users, our IT dept does not like me and won't change their stuff. I could automate moving the mouse to the OK location click the mouse!

[ByteMarks]

I could automate moving the mouse to the OK location click the mouse!

I don't think that is possible using Excel. Once the msgbox is displayed no code will run until it's dismissed.

You can't modify the IT file, but as it produces the msgbox, that's where changes would need to be made.

Obviously I don't know any detail, but generally I'd be hesitant to perform an upload in the BeforeSave event as it's vulnerable to data loss if something went wrong.

[6StringJazzer]

I have a concept but I have no idea how to implement this in VBA, or if it's possible. The idea would be to open the IT file without enabling macros. I have never done that in VBA but it's an avenue you might be able to pursue.

[ByteMarks]

Could try moving it to an untrusted location and adding data to it there before moving it back. I'm unsure if opening a file in an untrusted location with code would enable the macros in it though.

You'd then open the file again after being moved back to run the upload code and close without saving.

Something like this

1. check that the proper IT file isn't read only. Abort if it is.
2. copy it to an untrusted location
3. set the readonly, property of the real file to true,so if someone else tries the same it will abort.
4. open copy, add data,save and close.
5. replace the proper copy with the updated one
6. open it, run upload,close without saving

[Steveapa]

Thanks ByteMarks... You got me thinking.
Here is the solution... I disable events before opening the IT workbook - this stops workbook open from running. I run the macro in the IT file I want to run, I close and save without running the IT beforesave macro!

Application.EnableEvents = False
Set tep_pl_wb = Workbooks.Open(sharepoint_path)
Application.Run "'P&L.xlsm'!btnUpload_Click"
tep_pl_wb.Close (True)
Application.EnableEvents = True

[6StringJazzer]

Oh, that's a good idea. I had forgotten that disabling events is global to the entire Excel instance, and not specific to the workbook where the code to disable events is run.

[ByteMarks]

Great solution.