VBA macro works on one computer but when other use it, it attempts to save to the wrong save path and runtime errors

[ZMathis]

Hello,

I have a Macro that un-shares the workbook then re-shares it after the macro has run. It works for me and when re-sharing it, it saves to the correct file path given in the vba. But when others in the office try it, it adds 7 or 8 random numbers at the end of the save address, then Runtime Error 1004s. I was thinking it might have something to do with the enabling content because someone suggested having them manually save after enabling content to unflag themselves as non-enabled in the system, but, while it did run the macro up to the last line (the re-sharing part) where it hadn't been doing before (before it would give the same error but all the macro accomplished before erroring out was un-sharing it), it still is giving that runtime error. When I hit the debug, it highlights the line below, with the correct save file path.

ActiveWorkbook.SaveAs "x:\example\filename.xlsm", , , , , , xlShared

But the Runtime Error 1004 is stating that the file path doesn't exist "x:\example\filename\0342242", the numbers are different every time.

Thank you in advance for any guidance.

 

[johnnyL]

Should probably provide more code so we can see what the code is doing before that line.

 

[ZMathis]

It's not doing anything fancy it's just filling any formulas that got overwritten towards the end of the macro. The if just made sure certain criteria were met before running the macro. The 'Application.DisplayAlerts = False' happens right before the 'ActiveWorkbook.ExclusiveAccess'

 

[Alex Blakenburg]

This does not explain the random numbers on the end but if it is happening consistently for certain other users, it might be worth checking that their drive mapping for z: is identical to yours.

 

[ZMathis]

I'm military we're all mapped to the same thing same way. I'll confirm though.

 

[ZMathis]

I confirmed we're all mapped to the Z:\ . I know the lesser of all evils is to just make the user manually choose the save path every time, but, if I can "Army proof" it, I'd like to.

 

[johnnyL]

Military Intelligence

I only found one page when googling your situation, Random Characters <---

 

[ZMathis]

I tried to record macro the save as to see how the computer would write the code and I added what it spat out in the code below, so I attempted to add the share argument onto the end of what it did, the next comma in the SaveAs path was the XLAccessMode argument but I'm just getting the compiled error when trying to run it, anyone have input on how to fix this error? If so maybe the save path would be more defined for the computer to understand better??

 

[Alex Blakenburg]

Try Changing it
from xlSaveAsAccessMode:=xlShared
to AccessMode:=xlShared

Ref: Better Solutions > Excel Workbooks - Saving

 

[ZMathis]

Thanks that worked but, now it's back to square one where it works for me not others. Now I'm wondering if there's a function with the ActiveWorkbook.SaveAs to prompt the user to choose the save path, I've seen other threads about getting VBA to record what file path the user chooses but none of them use the SaveAs meaning it won't Share the Workbook.