Migration to cloud - Script to change excel formulas
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
That's my first question here, sorry for any mistake or any English mistake.
My company is moving from a local network to the cloud (with Microsoft Sharepoint).
We basically work with Excel spreadsheets (hundreds of them) and with a lot of "Vlookup", so our spreadsheets are all connected. We've hired a company to do this cloud process, but yesterday they told me that if we move our spreadsheet from our local network to the cloud, our "Vlookup" formulas will got broken.
My first idea is: a script to open the spreadsheet, find and change all the formulas with "=vlookup(A1,'serverfolder1[spreadsheet.xlsx]..." to "=vlookup(A1,'newpath[spreadsheet.xlsx]...".
As I have lots of spreadsheets, I need something that I can write the path to the spreadsheet inside the script and run, to do the process fast.
Using VBA I think is impossible, because the spreadsheets are not Macro-enabled and I don't want to open one by one and run a VBA, will take to long to finish.
I hope I made everything clear, my programming skills are very limited, but all the help are welcome.
Thanks!
microsoft-excel
asked Jan 31 at 13:50
LucasLucas
1
add a comment |
That's my first question here, sorry for any mistake or any English mistake.
My company is moving from a local network to the cloud (with Microsoft Sharepoint).
We basically work with Excel spreadsheets (hundreds of them) and with a lot of "Vlookup", so our spreadsheets are all connected. We've hired a company to do this cloud process, but yesterday they told me that if we move our spreadsheet from our local network to the cloud, our "Vlookup" formulas will got broken.
My first idea is: a script to open the spreadsheet, find and change all the formulas with "=vlookup(A1,'serverfolder1[spreadsheet.xlsx]..." to "=vlookup(A1,'newpath[spreadsheet.xlsx]...".
As I have lots of spreadsheets, I need something that I can write the path to the spreadsheet inside the script and run, to do the process fast.
Using VBA I think is impossible, because the spreadsheets are not Macro-enabled and I don't want to open one by one and run a VBA, will take to long to finish.
I hope I made everything clear, my programming skills are very limited, but all the help are welcome.
Thanks!
microsoft-excel
asked Jan 31 at 13:50
LucasLucas
1
1
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
1
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
If the spreadsheets are inxlsxformat this can be done in an easier manner. (Please address a comment me by adding@harrymcto the comment.)
– harrymc
Jan 31 at 15:25
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00
add a comment |
That's my first question here, sorry for any mistake or any English mistake.
My company is moving from a local network to the cloud (with Microsoft Sharepoint).
We basically work with Excel spreadsheets (hundreds of them) and with a lot of "Vlookup", so our spreadsheets are all connected. We've hired a company to do this cloud process, but yesterday they told me that if we move our spreadsheet from our local network to the cloud, our "Vlookup" formulas will got broken.
My first idea is: a script to open the spreadsheet, find and change all the formulas with "=vlookup(A1,'serverfolder1[spreadsheet.xlsx]..." to "=vlookup(A1,'newpath[spreadsheet.xlsx]...".
As I have lots of spreadsheets, I need something that I can write the path to the spreadsheet inside the script and run, to do the process fast.
Using VBA I think is impossible, because the spreadsheets are not Macro-enabled and I don't want to open one by one and run a VBA, will take to long to finish.
I hope I made everything clear, my programming skills are very limited, but all the help are welcome.
Thanks!
microsoft-excel
asked Jan 31 at 13:50
LucasLucas
1
That's my first question here, sorry for any mistake or any English mistake.
My company is moving from a local network to the cloud (with Microsoft Sharepoint).
We basically work with Excel spreadsheets (hundreds of them) and with a lot of "Vlookup", so our spreadsheets are all connected. We've hired a company to do this cloud process, but yesterday they told me that if we move our spreadsheet from our local network to the cloud, our "Vlookup" formulas will got broken.
My first idea is: a script to open the spreadsheet, find and change all the formulas with "=vlookup(A1,'serverfolder1[spreadsheet.xlsx]..." to "=vlookup(A1,'newpath[spreadsheet.xlsx]...".
As I have lots of spreadsheets, I need something that I can write the path to the spreadsheet inside the script and run, to do the process fast.
Using VBA I think is impossible, because the spreadsheets are not Macro-enabled and I don't want to open one by one and run a VBA, will take to long to finish.
I hope I made everything clear, my programming skills are very limited, but all the help are welcome.
Thanks!
microsoft-excel
microsoft-excel
asked Jan 31 at 13:50
LucasLucas
1
asked Jan 31 at 13:50
LucasLucas
1
asked Jan 31 at 13:50
LucasLucas
1
asked Jan 31 at 13:50
LucasLucas
1
asked Jan 31 at 13:50
LucasLucas
1
1
1
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
1
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
If the spreadsheets are inxlsxformat this can be done in an easier manner. (Please address a comment me by adding@harrymcto the comment.)
– harrymc
Jan 31 at 15:25
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00
add a comment |
1
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
1
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
If the spreadsheets are inxlsxformat this can be done in an easier manner. (Please address a comment me by adding@harrymcto the comment.)
– harrymc
Jan 31 at 15:25
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00
1
1
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
1
1
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
If the spreadsheets are in
xlsx format this can be done in an easier manner. (Please address a comment me by adding @harrymc to the comment.)– harrymc
Jan 31 at 15:25
If the spreadsheets are in
xlsx format this can be done in an easier manner. (Please address a comment me by adding @harrymc to the comment.)– harrymc
Jan 31 at 15:25
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00
add a comment |
2 Answers
2
active
oldest
votes
You asked for a script because you mention you think using VBA is impossible or that you need to open all files and then run the VBA.
You can use VBA to open files and run specific codes in them. It is totally possible to set a spreadsheet with a VBA code that will open a file, change something, save/close the file, open the next file and repeat.
Also, when you open a spreadsheet with a path that is no longer accessible as it was, it asks you to update the path because it didn't find it. You can use this to fix the spreadsheets as you use them, instead of fixing hundreds of spreadsheets when people only use ten of them.
I recommend you keep a backup for everything in cloud for a while before you leave it all in the cloud, AND a backup for everything before you mess with it. From experience, some people use the fact that you had to fix something to blame for mistakes they won't own.
answered Jan 31 at 14:00
MoacirMoacir
21415
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
add a comment |
If you wish to avoid painful VBA scripting, you could use the commercial
PowerGREP (139 euro, trial available).
PowerGREP is said to be able to
search Excel Speadsheets.
It can search both the XLS and XLSX formats without the help of Excel,
but to search-and-replace only XLSX files by searching through their raw XML.
You may test first the
free evaluation version.
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1400573%2fmigration-to-cloud-script-to-change-excel-formulas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You asked for a script because you mention you think using VBA is impossible or that you need to open all files and then run the VBA.
You can use VBA to open files and run specific codes in them. It is totally possible to set a spreadsheet with a VBA code that will open a file, change something, save/close the file, open the next file and repeat.
Also, when you open a spreadsheet with a path that is no longer accessible as it was, it asks you to update the path because it didn't find it. You can use this to fix the spreadsheets as you use them, instead of fixing hundreds of spreadsheets when people only use ten of them.
I recommend you keep a backup for everything in cloud for a while before you leave it all in the cloud, AND a backup for everything before you mess with it. From experience, some people use the fact that you had to fix something to blame for mistakes they won't own.
answered Jan 31 at 14:00
MoacirMoacir
21415
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
add a comment |
You asked for a script because you mention you think using VBA is impossible or that you need to open all files and then run the VBA.
You can use VBA to open files and run specific codes in them. It is totally possible to set a spreadsheet with a VBA code that will open a file, change something, save/close the file, open the next file and repeat.
Also, when you open a spreadsheet with a path that is no longer accessible as it was, it asks you to update the path because it didn't find it. You can use this to fix the spreadsheets as you use them, instead of fixing hundreds of spreadsheets when people only use ten of them.
I recommend you keep a backup for everything in cloud for a while before you leave it all in the cloud, AND a backup for everything before you mess with it. From experience, some people use the fact that you had to fix something to blame for mistakes they won't own.
answered Jan 31 at 14:00
MoacirMoacir
21415
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
add a comment |
You asked for a script because you mention you think using VBA is impossible or that you need to open all files and then run the VBA.
You can use VBA to open files and run specific codes in them. It is totally possible to set a spreadsheet with a VBA code that will open a file, change something, save/close the file, open the next file and repeat.
Also, when you open a spreadsheet with a path that is no longer accessible as it was, it asks you to update the path because it didn't find it. You can use this to fix the spreadsheets as you use them, instead of fixing hundreds of spreadsheets when people only use ten of them.
I recommend you keep a backup for everything in cloud for a while before you leave it all in the cloud, AND a backup for everything before you mess with it. From experience, some people use the fact that you had to fix something to blame for mistakes they won't own.
answered Jan 31 at 14:00
MoacirMoacir
21415
You asked for a script because you mention you think using VBA is impossible or that you need to open all files and then run the VBA.
You can use VBA to open files and run specific codes in them. It is totally possible to set a spreadsheet with a VBA code that will open a file, change something, save/close the file, open the next file and repeat.
Also, when you open a spreadsheet with a path that is no longer accessible as it was, it asks you to update the path because it didn't find it. You can use this to fix the spreadsheets as you use them, instead of fixing hundreds of spreadsheets when people only use ten of them.
I recommend you keep a backup for everything in cloud for a while before you leave it all in the cloud, AND a backup for everything before you mess with it. From experience, some people use the fact that you had to fix something to blame for mistakes they won't own.
answered Jan 31 at 14:00
MoacirMoacir
21415
answered Jan 31 at 14:00
MoacirMoacir
21415
answered Jan 31 at 14:00
MoacirMoacir
21415
answered Jan 31 at 14:00
MoacirMoacir
21415
21415
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
add a comment |
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
+1 for separate manual backups of the files, preferably in dated zipped folders of the whole set. Confusion and fingerpointing will be common, especially if this big change is done all on one day.
– Christopher Hostage
Jan 31 at 16:37
add a comment |
If you wish to avoid painful VBA scripting, you could use the commercial
PowerGREP (139 euro, trial available).
PowerGREP is said to be able to
search Excel Speadsheets.
It can search both the XLS and XLSX formats without the help of Excel,
but to search-and-replace only XLSX files by searching through their raw XML.
You may test first the
free evaluation version.
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
add a comment |
If you wish to avoid painful VBA scripting, you could use the commercial
PowerGREP (139 euro, trial available).
PowerGREP is said to be able to
search Excel Speadsheets.
It can search both the XLS and XLSX formats without the help of Excel,
but to search-and-replace only XLSX files by searching through their raw XML.
You may test first the
free evaluation version.
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
add a comment |
If you wish to avoid painful VBA scripting, you could use the commercial
PowerGREP (139 euro, trial available).
PowerGREP is said to be able to
search Excel Speadsheets.
It can search both the XLS and XLSX formats without the help of Excel,
but to search-and-replace only XLSX files by searching through their raw XML.
You may test first the
free evaluation version.
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
If you wish to avoid painful VBA scripting, you could use the commercial
PowerGREP (139 euro, trial available).
PowerGREP is said to be able to
search Excel Speadsheets.
It can search both the XLS and XLSX formats without the help of Excel,
but to search-and-replace only XLSX files by searching through their raw XML.
You may test first the
free evaluation version.
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
answered Jan 31 at 16:32
harrymcharrymc
265k14274583
265k14274583
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1400573%2fmigration-to-cloud-script-to-change-excel-formulas%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
To be fair, if you are using hundreds of Spreadsheets and need to connect the data between them, you shouldn't be using them and should use a Database system instead. Excel is not a Database system.
– Moacir
Jan 31 at 13:53
1
I agree with you Moacir, but that's the way my company work for the last seven years, and it's impossible to change by now, so I'll have to find a way to resolve this problem for them now.
– Lucas
Jan 31 at 14:01
If the spreadsheets are in
xlsxformat this can be done in an easier manner. (Please address a comment me by adding@harrymcto the comment.)– harrymc
Jan 31 at 15:25
@harrymc, most of our spreadsheets are in xlsx format, can you tell me more about this manner? Thank you.
– Lucas
Jan 31 at 16:00