Excel VBA how to autosave for seperate users
0
We have three different users working that can update one excel document using dropbox. When any of these users clicks 'save' I would like the document to save in a shared dropbox folder. Each user has a different directory though on their computer to this dropbox folder though.
Here is my code so far, which is simply trying to save the copy in user1's directory - and if it gets a 1004 error, it tries user2's directory etc.
This seems to work, but I am still getting the msgbox at the end. How do I break these If statements to that it stops once it has managed to save a copy?
Sub Auto_Save()
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim backupfolder As String
On Error Resume Next
user1 = "C:Usersuser1.siDropboxHub"
user2 = "C:Usersuser2DropboxHub Admin Folder"
user3 = "C:Usersuser3.siDropboxHub"
ThisWorkbook.SaveCopyAs Filename:=user1 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user2 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user3 & ThisWorkbook.Name
If Err.Number = 1004 Then
MsgBox "Could not save copy to your directory."
End If
End If
End If
End Sub
excel vba excel-vba
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
add a comment |
0
We have three different users working that can update one excel document using dropbox. When any of these users clicks 'save' I would like the document to save in a shared dropbox folder. Each user has a different directory though on their computer to this dropbox folder though.
Here is my code so far, which is simply trying to save the copy in user1's directory - and if it gets a 1004 error, it tries user2's directory etc.
This seems to work, but I am still getting the msgbox at the end. How do I break these If statements to that it stops once it has managed to save a copy?
Sub Auto_Save()
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim backupfolder As String
On Error Resume Next
user1 = "C:Usersuser1.siDropboxHub"
user2 = "C:Usersuser2DropboxHub Admin Folder"
user3 = "C:Usersuser3.siDropboxHub"
ThisWorkbook.SaveCopyAs Filename:=user1 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user2 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user3 & ThisWorkbook.Name
If Err.Number = 1004 Then
MsgBox "Could not save copy to your directory."
End If
End If
End If
End Sub
excel vba excel-vba
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
1
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"
– Nathan_Sav
Nov 15 '18 at 8:58
add a comment |
0
0
0
We have three different users working that can update one excel document using dropbox. When any of these users clicks 'save' I would like the document to save in a shared dropbox folder. Each user has a different directory though on their computer to this dropbox folder though.
Here is my code so far, which is simply trying to save the copy in user1's directory - and if it gets a 1004 error, it tries user2's directory etc.
This seems to work, but I am still getting the msgbox at the end. How do I break these If statements to that it stops once it has managed to save a copy?
Sub Auto_Save()
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim backupfolder As String
On Error Resume Next
user1 = "C:Usersuser1.siDropboxHub"
user2 = "C:Usersuser2DropboxHub Admin Folder"
user3 = "C:Usersuser3.siDropboxHub"
ThisWorkbook.SaveCopyAs Filename:=user1 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user2 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user3 & ThisWorkbook.Name
If Err.Number = 1004 Then
MsgBox "Could not save copy to your directory."
End If
End If
End If
End Sub
excel vba excel-vba
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
We have three different users working that can update one excel document using dropbox. When any of these users clicks 'save' I would like the document to save in a shared dropbox folder. Each user has a different directory though on their computer to this dropbox folder though.
Here is my code so far, which is simply trying to save the copy in user1's directory - and if it gets a 1004 error, it tries user2's directory etc.
This seems to work, but I am still getting the msgbox at the end. How do I break these If statements to that it stops once it has managed to save a copy?
Sub Auto_Save()
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim backupfolder As String
On Error Resume Next
user1 = "C:Usersuser1.siDropboxHub"
user2 = "C:Usersuser2DropboxHub Admin Folder"
user3 = "C:Usersuser3.siDropboxHub"
ThisWorkbook.SaveCopyAs Filename:=user1 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user2 & ThisWorkbook.Name
If Err.Number = 1004 Then
ThisWorkbook.SaveCopyAs Filename:=user3 & ThisWorkbook.Name
If Err.Number = 1004 Then
MsgBox "Could not save copy to your directory."
End If
End If
End If
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
edited Nov 15 '18 at 8:25
Pᴇʜ
21.2k42750
21.2k42750
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
asked Nov 15 '18 at 7:19
wazzahenrywazzahenry
5851828
5851828
1
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"
– Nathan_Sav
Nov 15 '18 at 8:58
add a comment |
1
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"
– Nathan_Sav
Nov 15 '18 at 8:58
1
1
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,
if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"– Nathan_Sav
Nov 15 '18 at 8:58
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,
if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"– Nathan_Sav
Nov 15 '18 at 8:58
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53314242%2fexcel-vba-how-to-autosave-for-seperate-users%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53314242%2fexcel-vba-how-to-autosave-for-seperate-users%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
The whole scenario does not make sense. If three users are working on the same workbook, you either have co-authoring enabled with Office 365, or they all work on a separate copy of the file (otherwise the file would be "locked for editing" to the other two). With the former, the file will be saved with version history, but remain in the same place. With the latter, they can't be editing the same file, so they all have their own copies and just save that copy wherever it lives with a simple Save command. What am I not getting?
– teylyn
Nov 15 '18 at 7:37
amended to mention it is a shared dropbox file.
– wazzahenry
Nov 15 '18 at 7:40
What is err.number after the save? Has it stayed at 1004?? Maybe best to check the folder exists rather than the error. Also, isnt this being called by the user using the sheet, so this can be done by code with no check,
if environ("username") = "User2" then strPath="C:Usersuser2DropboxHub Admin Folder"– Nathan_Sav
Nov 15 '18 at 8:58