Multiple users using Excel VBA to access external files leading to conflicted copies. Solutions or better...
In my position, we have around 15 users in the field. While in the field, they will likely be offline due to bad WiFi or not being able to use a hotspot due to bad service.
We have a shared dropbox folder with multiple database excel files that are updated with new information weekly, hence why we use the dropbox to keep everyone up to date.
We use in house made excel templates to perform our work. The VBA code accesses the shared dropbox files, these files are not changed, only opened and information copied from it, then closed without saving (but this does still lead to conflicted copies since things like "date accessed" changes in windows).
So my question is...Is there a better method to ensure that all the users have the most updated database files without having to manually update the file constantly. That will allow us to use excel VBA to access these automatically updated (by our office) files without causing large amounts of "conflicted copies"? Due to the nature of our work, these database files need to be accessible offline as well (which are the situations that lead to multiple conflicted copies haha)
Thanks.
excel vba excel-vba cloud dropbox
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
|
show 2 more comments
In my position, we have around 15 users in the field. While in the field, they will likely be offline due to bad WiFi or not being able to use a hotspot due to bad service.
We have a shared dropbox folder with multiple database excel files that are updated with new information weekly, hence why we use the dropbox to keep everyone up to date.
We use in house made excel templates to perform our work. The VBA code accesses the shared dropbox files, these files are not changed, only opened and information copied from it, then closed without saving (but this does still lead to conflicted copies since things like "date accessed" changes in windows).
So my question is...Is there a better method to ensure that all the users have the most updated database files without having to manually update the file constantly. That will allow us to use excel VBA to access these automatically updated (by our office) files without causing large amounts of "conflicted copies"? Due to the nature of our work, these database files need to be accessible offline as well (which are the situations that lead to multiple conflicted copies haha)
Thanks.
excel vba excel-vba cloud dropbox
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
Please show the code how you open the shared workbooks with VBA: It should be something like:Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)
– Pᴇʜ
Nov 16 '18 at 14:42
2
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
1
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51
|
show 2 more comments
In my position, we have around 15 users in the field. While in the field, they will likely be offline due to bad WiFi or not being able to use a hotspot due to bad service.
We have a shared dropbox folder with multiple database excel files that are updated with new information weekly, hence why we use the dropbox to keep everyone up to date.
We use in house made excel templates to perform our work. The VBA code accesses the shared dropbox files, these files are not changed, only opened and information copied from it, then closed without saving (but this does still lead to conflicted copies since things like "date accessed" changes in windows).
So my question is...Is there a better method to ensure that all the users have the most updated database files without having to manually update the file constantly. That will allow us to use excel VBA to access these automatically updated (by our office) files without causing large amounts of "conflicted copies"? Due to the nature of our work, these database files need to be accessible offline as well (which are the situations that lead to multiple conflicted copies haha)
Thanks.
excel vba excel-vba cloud dropbox
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
In my position, we have around 15 users in the field. While in the field, they will likely be offline due to bad WiFi or not being able to use a hotspot due to bad service.
We have a shared dropbox folder with multiple database excel files that are updated with new information weekly, hence why we use the dropbox to keep everyone up to date.
We use in house made excel templates to perform our work. The VBA code accesses the shared dropbox files, these files are not changed, only opened and information copied from it, then closed without saving (but this does still lead to conflicted copies since things like "date accessed" changes in windows).
So my question is...Is there a better method to ensure that all the users have the most updated database files without having to manually update the file constantly. That will allow us to use excel VBA to access these automatically updated (by our office) files without causing large amounts of "conflicted copies"? Due to the nature of our work, these database files need to be accessible offline as well (which are the situations that lead to multiple conflicted copies haha)
Thanks.
excel vba excel-vba cloud dropbox
excel vba excel-vba cloud dropbox
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
edited Nov 16 '18 at 14:38
Pᴇʜ
20.6k42650
20.6k42650
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
asked Nov 16 '18 at 14:35
Kyle GilleyKyle Gilley
1
1
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
Please show the code how you open the shared workbooks with VBA: It should be something like:Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)
– Pᴇʜ
Nov 16 '18 at 14:42
2
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
1
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51
|
show 2 more comments
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
Please show the code how you open the shared workbooks with VBA: It should be something like:Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)
– Pᴇʜ
Nov 16 '18 at 14:42
2
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
1
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
Please show the code how you open the shared workbooks with VBA: It should be something like:
Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)– Pᴇʜ
Nov 16 '18 at 14:42
Please show the code how you open the shared workbooks with VBA: It should be something like:
Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)– Pᴇʜ
Nov 16 '18 at 14:42
2
2
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
1
1
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51
|
show 2 more comments
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
});
}
});
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%2f53339893%2fmultiple-users-using-excel-vba-to-access-external-files-leading-to-conflicted-co%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
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.
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%2f53339893%2fmultiple-users-using-excel-vba-to-access-external-files-leading-to-conflicted-co%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
Open the files as "read only" then nothing should be changed. And you should not get any conflicts.
– Pᴇʜ
Nov 16 '18 at 14:40
The conflicts still occur when making them "read only". Will I need to make them read only on every computer? Actually one of the previously made "read only" files seems to have been made not "read only" Hopefully this is just user error then haha,
– Kyle Gilley
Nov 16 '18 at 14:41
Please show the code how you open the shared workbooks with VBA: It should be something like:
Set myWb = Application.Workbooks.Open(Filename:="myfile", ReadOnly:=True)– Pᴇʜ
Nov 16 '18 at 14:42
2
"users have the most updated database files" Use a database instead of Excel. Excel isn't a database. If your front-end is in Excel, the backend could be Access and it would solve 99% of the problem.
– FreeMan
Nov 16 '18 at 14:49
1
If you choose a database I recommend to use a real one (eg SQL) instead of MS-Access.
– Pᴇʜ
Nov 16 '18 at 14:51