Saving file using VBA on Dropbox for business

[Magic_Sponge]

Hello everyone. I'm really hoping someone can help as this one has really stumped me and I've searched all afternoon for the answer but can't find anything.

My issue is I've just moved to a new company which uses Dropbox (first time for me) and so I'm struggling to work out how to do things. Essentially I have a folder which is shared with multiple users. In this folder, there are three sub-folders which represents the 'lifecycle' of a piece of work the users need to complete. So it moves from 'To be reviewed' to 'To be Approved' to 'Approved'. I have code in the document which, when the button is clicked, saves it in the next folder and then kills the file in the original folder.

After some quick searching I found I could use Environ("Homepath") to get the correct address for the users. But the weird thing is, when one of the users tries to click the first button, it comes up with error 1004 saying it can't find the next folder, even though it works fine for me. The really weird thing is the path it shows looks right to me except it adds a whole load of random characters at the end of the path string.

The line of code that trips up is

ActiveWorkbook.SaveAs Environ("HOMEPATH") & "\Dropbox Name\Shared Folder\To Be Approved\File.xlsm"

Like I say, it works fine on my PC. I asked one of the team to test it, and got the following error message:

Run-time error '1004':

Microsoft Excel cannot access the file 'C:\Users\User Name\Dropbox Name\Shared Folder\To Be Approved\324CA200'. There are several possible reasons:

• The file name or path does not exist
• The file is being used by another program
• The workbook you are trying to save has the same name as...

Unfortunately the snip I was sent cut-off at this point. I understand why it can't find it with those random characters at the end, what I don't understand is how they got there! They also change each time he tries. Like I say, this Dropbox stuff is all new to me so hoping it's a simple fix and will be eternally grateful for any help at all! More than happy to hear ideas for a different approach, but I'm going to have to rethink how to do this otherwise.

 

[GWteB]

Hi & Welcome to MrExcel!

Environ("HOMEPATH")

provides a path without a preceding logical drive letter, so Excel starts to argue...

Instead you better use:

Environ("userprofile")

EDIT:
In addition you should use a proper FileFormat parameter, so the line of code could look like:

ActiveWorkbook.SaveAs Environ("userprofile") & "\Dropbox Name\Shared Folder\To Be Approved\File.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[Magic_Sponge]

Fantastic, thank you so much for the quick response! Should have done this earlier...

I will give this a go tomorrow with fingers crossed and will be sure to let you know if successful.

Thanks again

 

[GWteB]

You are welcome and hopefully it resolves your issue.

 

[Magic_Sponge]

It didn't work I'm afraid ?. The characters at the end of the string were different again (2CB19300). I'm vaguely interested in what these numbers are, are they just completely random? And if so, why?? But not really that important. For what it's worth, the path in the error message that it said it couldn't find was the same as before, besides those 'random' characters at the end.

 

[GWteB]

The characters at the end of the string were different again (2CB19300).

This statement is interesting because you are constructing a string in your code, like Environ("userprofile") & "\Dropbox Name\Shared Folder\To Be Approved\File.xlsm"
Am I allowed to conclude that a file with the name 2CB19300 is saved in that folder, or am I wrong?

 

[Magic_Sponge]

That's what it certainly looks like it's trying to do, but there is no file with that name in the folder or anywhere else. First time I tried this the file name was created using

ActiveWorkbook.Name

. Then I tried adding the workbook name to a cell in a worksheet and referenced it that way. Then I even tried hard coding the name of the file in the code itself, but I'm still coming up with those characters.

Even more weirdly, I added a msgbox popup to show me what it thought the path and filename string was and it came up with the correct string. But then after clicking 'ok' to move on to the saving part of the code, it still went wrong with those random characters. All very strange!

 

[GWteB]

I'm also using DropBox but I can't reproduce your problem, at least not with a regular macro enabled workbook (.xlsm).

I once had a similar issue with an AddIn (.xlam) and thought that might be the cause of your issue. That's why I asked whether a file with the name 2CB19300 was saved.
FYI, the relevant AddIn saved a number of settings on a worksheet and in that regard the AddIn had to save itself. The developer of the AddIn accidentally used the SaveAs method for that, resulting in the AddIn being saved with a (temporary) file name with a hexadecimal appearance as you described. That file name was different each time the SaveAs method was invoked, so in the end multiple files were saved on disk. Because the AddIn never got saved with its own file name, the settings were therefore not saved either. As a result, the incorrect use of the SaveAs method was quickly discovered; of course it should have just been the Save method.

As it appears, such behavior isn't the case at your side. Unless there's another Workbook or AddIn running that intercepts the Workbook_BeforeSave event at the Excel application level, I couldn't explain the behavior you described.

Finally, I would like to note that you are using the ActiveWorkbook object. This object doesn't necessarily refer to the workbook you are trying to save. If it concerns the workbook that also contains your VBA code, I would recommend using the ThisWorkbook object.

 

[Magic_Sponge]

Thanks very much for your help, I've tried a few different things now and it all seems to end the same! I do wonder whether the user's setup who is testing it for me is causing the issue, but fundamentally that doesn't help me anyway as he would need to be able to use it anyway!

Thanks again for the advice, if I do have a breakthrough I'll post the details here for future.

 

[GWteB]

You're welcome. Too bad you don't have a solution yet. Thanks for the follow-up, I'll keep watching ...