Saving file using VBA on Dropbox for business

Magic_Sponge

New Member
Joined
Oct 28, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
Hi & Welcome to MrExcel!

VBA Code:
Environ("HOMEPATH")
provides a path without a preceding logical drive letter, so Excel starts to argue...

Instead you better use:
VBA Code:
Environ("userprofile")

EDIT:
In addition you should use a proper FileFormat parameter, so the line of code could look like:
VBA Code:
ActiveWorkbook.SaveAs Environ("userprofile") & "\Dropbox Name\Shared Folder\To Be Approved\File.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
Last edited:

Magic_Sponge

New Member
Joined
Oct 28, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and hopefully it resolves your issue.
 

Magic_Sponge

New Member
Joined
Oct 28, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 28, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 28, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You're welcome. Too bad you don't have a solution yet. Thanks for the follow-up, I'll keep watching ...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,263
Messages
5,836,289
Members
430,417
Latest member
Hallow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top