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.

 

[ebjacob1978]

Hello
Is there a solution for multiple excel files connected to each other through power query, works fine my with PC having the dropbox in C: drive, but the moment I check the same from another computer, the links don't work.

 

[Magic_Sponge]

Hi - just wanted to update that I found a solution on another website here which seemed to work for me (the one at the bottom). Can't say it will work for everyone but it has seemed to have done the trick for me.

 

[GWteB]

Glad you've got a solution. Thanks for letting us know. I will point another forum member to your solution.

 

[Sven001]

Hello Magic Sponge

Were you ever able to find a solution?

As I am sitting with same Problem With DROPBOX, my path does not want to save in the file.

Keep getting error 1004.

 

[RoryA]

Were you ever able to find a solution?

See post #12 (marked as the solution )