Saving xlsm, after running code I cannot save my worksheet in current location
0
I run a vbscript to open my excel sheet and run a macro.
however after the macro is run and I try to save it, I get this error.
I notice the location in which the file picker is not the original location, that is why this error message is coming up.
How can I keep the location the same after running my vbscript?
Here is my vbscript:
Set appExcel = createObject("Excel.Application")
set fso = CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.GetAbsolutePathName(".")
appExcel.Application.Visible = True
appExcel.Application.DisplayAlerts = False
appExcel.Application.AskToUpdateLinks = False
appExcel.Application.EnableEvents = True
appExcel.Workbooks.Open(CurrentDirectory & "excelsheet")
appExcel.Application.Run "Run_it"
appExcel.Application.EnableEvents = False
Set appExcel = Nothing
Set fso = Nothing
Thanks.
microsoft-excel
asked Feb 1 at 16:22
excelguyexcelguy
758
|
show 9 more comments
0
I run a vbscript to open my excel sheet and run a macro.
however after the macro is run and I try to save it, I get this error.
I notice the location in which the file picker is not the original location, that is why this error message is coming up.
How can I keep the location the same after running my vbscript?
Here is my vbscript:
Set appExcel = createObject("Excel.Application")
set fso = CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.GetAbsolutePathName(".")
appExcel.Application.Visible = True
appExcel.Application.DisplayAlerts = False
appExcel.Application.AskToUpdateLinks = False
appExcel.Application.EnableEvents = True
appExcel.Workbooks.Open(CurrentDirectory & "excelsheet")
appExcel.Application.Run "Run_it"
appExcel.Application.EnableEvents = False
Set appExcel = Nothing
Set fso = Nothing
Thanks.
microsoft-excel
asked Feb 1 at 16:22
excelguyexcelguy
758
What isCurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're.Open()the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just..."excelsheet.xlsx")?
– BruceWayne
Feb 1 at 16:47
CurrentDirectoryis correct. I dont tell the file to save, i save it manually after the macroRun_ithas been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in theNetwork Locationfolder when i do try to just "save".
– excelguy
Feb 1 at 17:27
1
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type?thisworkbook.FullName- what comes back? Is it a "legal" path, or are there errors/odd characters in there?
– BruceWayne
Feb 1 at 17:30
Y:superuserexcelsheet.xlsm. Why is itY:thats abit weird
– excelguy
Feb 1 at 19:50
AndCurrentDirectoryis not in theYdrive? Do you have aYdrive?
– BruceWayne
Feb 1 at 19:58
|
show 9 more comments
0
0
0
I run a vbscript to open my excel sheet and run a macro.
however after the macro is run and I try to save it, I get this error.
I notice the location in which the file picker is not the original location, that is why this error message is coming up.
How can I keep the location the same after running my vbscript?
Here is my vbscript:
Set appExcel = createObject("Excel.Application")
set fso = CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.GetAbsolutePathName(".")
appExcel.Application.Visible = True
appExcel.Application.DisplayAlerts = False
appExcel.Application.AskToUpdateLinks = False
appExcel.Application.EnableEvents = True
appExcel.Workbooks.Open(CurrentDirectory & "excelsheet")
appExcel.Application.Run "Run_it"
appExcel.Application.EnableEvents = False
Set appExcel = Nothing
Set fso = Nothing
Thanks.
microsoft-excel
asked Feb 1 at 16:22
excelguyexcelguy
758
I run a vbscript to open my excel sheet and run a macro.
however after the macro is run and I try to save it, I get this error.
I notice the location in which the file picker is not the original location, that is why this error message is coming up.
How can I keep the location the same after running my vbscript?
Here is my vbscript:
Set appExcel = createObject("Excel.Application")
set fso = CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.GetAbsolutePathName(".")
appExcel.Application.Visible = True
appExcel.Application.DisplayAlerts = False
appExcel.Application.AskToUpdateLinks = False
appExcel.Application.EnableEvents = True
appExcel.Workbooks.Open(CurrentDirectory & "excelsheet")
appExcel.Application.Run "Run_it"
appExcel.Application.EnableEvents = False
Set appExcel = Nothing
Set fso = Nothing
Thanks.
microsoft-excel
microsoft-excel
asked Feb 1 at 16:22
excelguyexcelguy
758
asked Feb 1 at 16:22
excelguyexcelguy
758
edited Feb 1 at 17:25
excelguy
asked Feb 1 at 16:22
excelguyexcelguy
758
asked Feb 1 at 16:22
excelguyexcelguy
758
asked Feb 1 at 16:22
excelguyexcelguy
758
758
What isCurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're.Open()the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just..."excelsheet.xlsx")?
– BruceWayne
Feb 1 at 16:47
CurrentDirectoryis correct. I dont tell the file to save, i save it manually after the macroRun_ithas been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in theNetwork Locationfolder when i do try to just "save".
– excelguy
Feb 1 at 17:27
1
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type?thisworkbook.FullName- what comes back? Is it a "legal" path, or are there errors/odd characters in there?
– BruceWayne
Feb 1 at 17:30
Y:superuserexcelsheet.xlsm. Why is itY:thats abit weird
– excelguy
Feb 1 at 19:50
AndCurrentDirectoryis not in theYdrive? Do you have aYdrive?
– BruceWayne
Feb 1 at 19:58
|
show 9 more comments
What isCurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're.Open()the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just..."excelsheet.xlsx")?
– BruceWayne
Feb 1 at 16:47
CurrentDirectoryis correct. I dont tell the file to save, i save it manually after the macroRun_ithas been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in theNetwork Locationfolder when i do try to just "save".
– excelguy
Feb 1 at 17:27
1
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type?thisworkbook.FullName- what comes back? Is it a "legal" path, or are there errors/odd characters in there?
– BruceWayne
Feb 1 at 17:30
Y:superuserexcelsheet.xlsm. Why is itY:thats abit weird
– excelguy
Feb 1 at 19:50
AndCurrentDirectoryis not in theYdrive? Do you have aYdrive?
– BruceWayne
Feb 1 at 19:58
What is
CurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're .Open() the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like ...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just ..."excelsheet.xlsx")?– BruceWayne
Feb 1 at 16:47
What is
CurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're .Open() the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like ...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just ..."excelsheet.xlsx")?– BruceWayne
Feb 1 at 16:47
CurrentDirectory is correct. I dont tell the file to save, i save it manually after the macro Run_it has been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in the Network Location folder when i do try to just "save".– excelguy
Feb 1 at 17:27
CurrentDirectory is correct. I dont tell the file to save, i save it manually after the macro Run_it has been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in the Network Location folder when i do try to just "save".– excelguy
Feb 1 at 17:27
1
1
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type
?thisworkbook.FullName - what comes back? Is it a "legal" path, or are there errors/odd characters in there?– BruceWayne
Feb 1 at 17:30
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type
?thisworkbook.FullName - what comes back? Is it a "legal" path, or are there errors/odd characters in there?– BruceWayne
Feb 1 at 17:30
Y:superuserexcelsheet.xlsm . Why is it Y: thats abit weird– excelguy
Feb 1 at 19:50
Y:superuserexcelsheet.xlsm . Why is it Y: thats abit weird– excelguy
Feb 1 at 19:50
And
CurrentDirectory is not in the Y drive? Do you have a Y drive?– BruceWayne
Feb 1 at 19:58
And
CurrentDirectory is not in the Y drive? Do you have a Y drive?– BruceWayne
Feb 1 at 19:58
|
show 9 more comments
0
active
oldest
votes
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
});
}
});
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%2fsuperuser.com%2fquestions%2f1401054%2fsaving-xlsm-after-running-code-i-cannot-save-my-worksheet-in-current-location%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 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.
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%2fsuperuser.com%2fquestions%2f1401054%2fsaving-xlsm-after-running-code-i-cannot-save-my-worksheet-in-current-location%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
What is
CurrentDirectory? Where do you tell the file to save? What's the expected file save location, and the one Excel keeps using? Does your title question relate to the body? As far as I can tell, it's an issue when you're.Open()the file, not saving. Also, how do you expect to open a directory? Aren't you missing something like...Open(CurrentDirectory & "excelsheetmyFile.xlsx")? Or just..."excelsheet.xlsx")?– BruceWayne
Feb 1 at 16:47
CurrentDirectoryis correct. I dont tell the file to save, i save it manually after the macroRun_ithas been ran. I have to go to save as and go to the location i want to save it. I can't just "save". its trying to save it in theNetwork Locationfolder when i do try to just "save".– excelguy
Feb 1 at 17:27
1
When you have the file open, before you save manually, in the Immediate Window (press CTRL+G in the VBEditor), type
?thisworkbook.FullName- what comes back? Is it a "legal" path, or are there errors/odd characters in there?– BruceWayne
Feb 1 at 17:30
Y:superuserexcelsheet.xlsm. Why is itY:thats abit weird– excelguy
Feb 1 at 19:50
And
CurrentDirectoryis not in theYdrive? Do you have aYdrive?– BruceWayne
Feb 1 at 19:58