Python win32com.client and “with” statement
Good afternoon,
I am writing some ETL scripts with Python, and am currently using win32com.client to open and refresh some data connections in Excel.
My question is this: should I be using a with statement to open/close "Excel.Application" as such
import win32com.client
xl = win32com.client.DispatchEx("Excel.Application")
def wb_ref(file):
xl.DisplayAlerts = False
with xl.workbooks.open(file) as wb:
wb.RefreshAll()
wb.Save()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
When I have tried this an exception occurs, so I am obviously not using it correctly.
Traceback (most recent call last):
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 32, in <module>
wb_ref( 'C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 11, in wb_ref
with xl.workbooks.open(file) as wb:
AttributeError: __enter__
Or do I need to explicitly call the close command
def wb_ref(file):
xl.DisplayAlerts = False
wb = xl.workbooks.open(file)
wb.RefreshAll()
wb.Save()
wb.Close()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
The second example is what I have been using, and it works. I guess I am just wondering what is the more pythonic way to script the above function.
(fyi - first time asker, longtime reader)
python pywin32 with-statement excel.application
asked Nov 17 '18 at 20:01
smugssmugs
236
add a comment |
Good afternoon,
I am writing some ETL scripts with Python, and am currently using win32com.client to open and refresh some data connections in Excel.
My question is this: should I be using a with statement to open/close "Excel.Application" as such
import win32com.client
xl = win32com.client.DispatchEx("Excel.Application")
def wb_ref(file):
xl.DisplayAlerts = False
with xl.workbooks.open(file) as wb:
wb.RefreshAll()
wb.Save()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
When I have tried this an exception occurs, so I am obviously not using it correctly.
Traceback (most recent call last):
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 32, in <module>
wb_ref( 'C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 11, in wb_ref
with xl.workbooks.open(file) as wb:
AttributeError: __enter__
Or do I need to explicitly call the close command
def wb_ref(file):
xl.DisplayAlerts = False
wb = xl.workbooks.open(file)
wb.RefreshAll()
wb.Save()
wb.Close()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
The second example is what I have been using, and it works. I guess I am just wondering what is the more pythonic way to script the above function.
(fyi - first time asker, longtime reader)
python pywin32 with-statement excel.application
asked Nov 17 '18 at 20:01
smugssmugs
236
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22
add a comment |
Good afternoon,
I am writing some ETL scripts with Python, and am currently using win32com.client to open and refresh some data connections in Excel.
My question is this: should I be using a with statement to open/close "Excel.Application" as such
import win32com.client
xl = win32com.client.DispatchEx("Excel.Application")
def wb_ref(file):
xl.DisplayAlerts = False
with xl.workbooks.open(file) as wb:
wb.RefreshAll()
wb.Save()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
When I have tried this an exception occurs, so I am obviously not using it correctly.
Traceback (most recent call last):
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 32, in <module>
wb_ref( 'C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 11, in wb_ref
with xl.workbooks.open(file) as wb:
AttributeError: __enter__
Or do I need to explicitly call the close command
def wb_ref(file):
xl.DisplayAlerts = False
wb = xl.workbooks.open(file)
wb.RefreshAll()
wb.Save()
wb.Close()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
The second example is what I have been using, and it works. I guess I am just wondering what is the more pythonic way to script the above function.
(fyi - first time asker, longtime reader)
python pywin32 with-statement excel.application
asked Nov 17 '18 at 20:01
smugssmugs
236
Good afternoon,
I am writing some ETL scripts with Python, and am currently using win32com.client to open and refresh some data connections in Excel.
My question is this: should I be using a with statement to open/close "Excel.Application" as such
import win32com.client
xl = win32com.client.DispatchEx("Excel.Application")
def wb_ref(file):
xl.DisplayAlerts = False
with xl.workbooks.open(file) as wb:
wb.RefreshAll()
wb.Save()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
When I have tried this an exception occurs, so I am obviously not using it correctly.
Traceback (most recent call last):
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 32, in <module>
wb_ref( 'C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 11, in wb_ref
with xl.workbooks.open(file) as wb:
AttributeError: __enter__
Or do I need to explicitly call the close command
def wb_ref(file):
xl.DisplayAlerts = False
wb = xl.workbooks.open(file)
wb.RefreshAll()
wb.Save()
wb.Close()
wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
The second example is what I have been using, and it works. I guess I am just wondering what is the more pythonic way to script the above function.
(fyi - first time asker, longtime reader)
python pywin32 with-statement excel.application
python pywin32 with-statement excel.application
asked Nov 17 '18 at 20:01
smugssmugs
236
asked Nov 17 '18 at 20:01
smugssmugs
236
edited Nov 18 '18 at 17:59
smugs
asked Nov 17 '18 at 20:01
smugssmugs
236
asked Nov 17 '18 at 20:01
smugssmugs
236
asked Nov 17 '18 at 20:01
smugssmugs
236
236
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22
add a comment |
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22
add a comment |
1 Answer
1
active
oldest
votes
You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.
If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:
from contextlib import closing
def wb_ref(file):
xl.DisplayAlerts = False
with closing(xl.workbooks.open(file)) as wb:
wb.RefreshAll()
wb.Save()
contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
add a comment |
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%2f53355048%2fpython-win32com-client-and-with-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.
If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:
from contextlib import closing
def wb_ref(file):
xl.DisplayAlerts = False
with closing(xl.workbooks.open(file)) as wb:
wb.RefreshAll()
wb.Save()
contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
add a comment |
You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.
If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:
from contextlib import closing
def wb_ref(file):
xl.DisplayAlerts = False
with closing(xl.workbooks.open(file)) as wb:
wb.RefreshAll()
wb.Save()
contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
add a comment |
You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.
If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:
from contextlib import closing
def wb_ref(file):
xl.DisplayAlerts = False
with closing(xl.workbooks.open(file)) as wb:
wb.RefreshAll()
wb.Save()
contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.
If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:
from contextlib import closing
def wb_ref(file):
xl.DisplayAlerts = False
with closing(xl.workbooks.open(file)) as wb:
wb.RefreshAll()
wb.Save()
contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
answered Nov 18 '18 at 9:47
snakecharmerbsnakecharmerb
10.1k42249
10.1k42249
add a comment |
add a comment |
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%2f53355048%2fpython-win32com-client-and-with-statement%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
you've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH
– landru27
Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question.
– smugs
Nov 17 '18 at 20:22