Morning Sven, How can we perform a vlookup using pandas in an existing worksheet instead of creating an output file ? Thank you! Have a great day ! Greg
@@gregorydubois8468 Hi Greg. Thanks for watching the video & your question. Please check out the following video: th-cam.com/video/AHS925L8JVk/w-d-xo.html Towards the end of the tutorial, I explain how to return the new (merged) dataframe to an existing worksheet. I hope this helps! Cheers, Sven
Well, I did not see your video several years ago when I was learning python. I used VBA a lot, so I can do whatever I want with it. I tried using python on the same jobs in Excel. Yes, it can do them too. As you said, the biggest obstacle in distributing a python application is that the other user most probably does not have python installed in his computer. Thus, after learning the basics of python, trying to write a couple of games, and writing modules to solve Excel problems, I pretty much give up on python.
Hi can you make a video on automation of pivots tables using Python and ML... like we put prepare pivot tables from raw data and how to automate those along with pivot fields.
Hey, I am trying to paste the data using a data frame but then it is pasting only int/float values keeping the strings blank. Could you please help me with this
Thanks you again Sir Sven. I will study it now. I really need more of excel automation examples project. Especially the copy pasting different range of cells value from of different excel workbook source and into master excel workbook, I would really appreciate if you do exactly the same example project, please
Hi Sven. Appreciate your kind assistance to my project please sht_HSDPA_Accessibility = wb_2G_3G_KPI.sheets['raw'] sht_master = wb_master.sheets['3G HSDPA Accessibility'] HSDPA_Accessibility_values = sht_HSDPA_Accessibility.range('H2:H25').value sht_master.range('AN2').options(transpose=True).value = HSDPA_Accessibility_values sht_HSDPA_Retainability = wb_2G_3G_KPI.sheets['raw'] sht_master = wb_master.sheets['3G HSDPA Retainability'] HSDPA_Retainability_values = sht_HSDPA_Retainability.range('I2:I25').value sht_master.range('AM2').options(transpose=True).value = HSDPA_Retainability_values The "value" from the source work sheet range H2: H25; I2:I25 are all in formula therefore, please help me what is the code to copy paste "value" to the AN2 because copying it will result to "0" if not copy paste in value.
@@noeldoller2958 Thank you for watching the video your comment, Noel Doller. Regarding your questions, you could add more parameters to the 'paste' function. Example to paste only values: sht_master.range("A1").paste(paste='values') Complete documentation can be found here: docs.xlwings.org/en/stable/api.html#xlwings.Range.paste I hope this helps!
@@CodingIsFun Thank you so much Sir for your guidance . it works fine now. I used the: range(A2).end(right).offset(0,1).options(transpose=True).value = HSDPA_Retainability_values Much appreciated Sir
Hi brother Can you help me Out?! As I want to create a data Entry Form Sheet from which data is saved of different different columns and on multiple sheets. Please guide me
@@CodingIsFun thank you very much . I am on leave for two weeks , ill try to have Access to files and give you an example . we are five persons to do the burring staff To do the Boring stuff full time
Hai sir, ur video super. I learn so much ur channel. I have one doubt I'm using excel data python. Excel sheet cells I have created formula. Excel read data python program only copy formula not value. Please help me I need excel rows read values only. Please advise sir.
I am unsure what you are trying to achieve, but you might want to use openpyxl. Please find below an example: from pathlib import Path import openpyxl excel_file = Path(r"C:\Users\UserName\Desktop\data.xlsx") wb = openpyxl.load_workbook(excel_file, data_only=True) Happy Coding!
hi Sven, 2:57 why the column sales need to be written as df[" Sales"] instead of df["Sales"] - with and without space in front of the word Sales. What's the difference? 🤓
Hi Sir, Great video. Sir I tried using the same method but if the sheet has some blanks columns in between then how can i copy paste all the data in the sheet?
@@CodingIsFun sir actually i am automating the process for like 20 sheets so i cannot write it manually The used_range option works for now Thanks a lot
How can I automate an excel file which can give me daily report? Right now I download an excel file then I use vlook up to find 7 KPIs for a call center. Can you guide, sir
Thanks for watching the video & your question. Without knowing your exact use-case, you could try to host your Python script, which performs the vlookup, on Pythonanywhere (www.pythonanywhere.com/). With pandas, you can also read in Excel files, which are stored on a web server. I hope this helps!
@@CodingIsFun thanks for your reply. Can you show some automation stuff on excel through which a daily report can be automated. Like in macros - we just update the data and everything comes as output. I have a sample report - would it be possible for you to make a video on it? I am from call center and we have lot of reporting stuff which takes lot of time.
@@ketuscorpion3717 Feel free to send me your example files via mail to: contact@pythonandvba.com I will see what I can do, *BUT* I cannot make any promises. I hope you can understand. Thank you!
And yess please also knows how we can do sedual run of our file daily basis with help of python . Right now i m doing with VBA and window task sechualler.
can you solve that problem for me? df = df.append(pd.read_excel(file), ignore_index = True) /Users/vplap73/Documents/excel/a.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
Thanks for watching the video & your comment. What exactly do you mean by VBA automation tasks with Python? Can you perhaps elaborate on what it is you're actually trying to achieve? Please provide some additional info. Thanks!
*I will be here in the comments section. For any issues, please provide your exact error message, and I will try to help.*
Hi Sven
How i Can refresh juste one single sheet in a workbook with several sheets . I am beginner . And for now i use pandas and openpyxl ?
Morning Sven,
How can we perform a vlookup using pandas in an existing worksheet instead of creating an output file ?
Thank you!
Have a great day !
Greg
@@gregorydubois8468 Hi Greg. Thanks for watching the video & your question. Please check out the following video:
th-cam.com/video/AHS925L8JVk/w-d-xo.html
Towards the end of the tutorial, I explain how to return the new (merged) dataframe to an existing worksheet.
I hope this helps!
Cheers,
Sven
@@CodingIsFun thank you Sven! Just have to apply it now 😁
Hi Sven
I used what you explained in your video and I'm saving so much time now !!!
Thanks a million.
Greg
Perfect motivation and inspiration to automate some SAP reportings!
Glad it was helpful! Thanks for watching & leaving a comment. BTW, cool profile pic ;)
Well, I did not see your video several years ago when I was learning python. I used VBA a lot, so I can do whatever I want with it. I tried using python on the same jobs in Excel. Yes, it can do them too. As you said, the biggest obstacle in distributing a python application is that the other user most probably does not have python installed in his computer. Thus, after learning the basics of python, trying to write a couple of games, and writing modules to solve Excel problems, I pretty much give up on python.
Thanks for watching and sharing your thoughts! 👍
Really admire your work.....Learning a lot
Thank you very much! I am happy to hear that you have been finding the videos helpful. 😃
Danke shon Sven! Good 👍 job
Vielen Dank! Glad you liked it! 😃
More like this! Thanks a lot Sven
Thanks for watching the video. I am planning to create more videos on Excel automation using Python. Stay tuned! ✌
Very informative to ease task
Glad you liked it. Thanks for watching and taking the time to leave a comment! Cheres, Sven ✌️
Amazing tutorial, thank you !
Glad you liked it. Thanks for watching!
Love it! Thank you! ❤️
Thanks for leaving a comment and for taking the time to watch! Glad you liked it. ♥
An additional disadvantage of VBA is that it is no longer being developed / improved by Microsoft. Basically, its as good now as it will ever be.
You are absolutely right! There will be no new VBA features.
Hi can you make a video on automation of pivots tables using Python and ML... like we put prepare pivot tables from raw data and how to automate those along with pivot fields.
Thanks for watching the video & your video suggestion. Sounds like an exciting video topic. I cannot make any promises, but l will see what I can do.
Hey, I am trying to paste the data using a data frame but then it is pasting only int/float values keeping the strings blank. Could you please help me with this
I am not quite sure to which step of the video you are referring to
Thanks for this vidéo, but I have error message when I use your code : attributeError : ´Range ´ object has no attribute ´ copy ´. Can you help me ?
Please update xlwings: "pip install xlwings --upgrade" and try again
Thanks you again Sir Sven. I will study it now. I really need more of excel automation examples project. Especially the copy pasting different range of cells value from of different excel workbook source and into master excel workbook, I would really appreciate if you do exactly the same example project, please
Hi Sven. Appreciate your kind assistance to my project please
sht_HSDPA_Accessibility = wb_2G_3G_KPI.sheets['raw']
sht_master = wb_master.sheets['3G HSDPA Accessibility']
HSDPA_Accessibility_values = sht_HSDPA_Accessibility.range('H2:H25').value
sht_master.range('AN2').options(transpose=True).value = HSDPA_Accessibility_values
sht_HSDPA_Retainability = wb_2G_3G_KPI.sheets['raw']
sht_master = wb_master.sheets['3G HSDPA Retainability']
HSDPA_Retainability_values = sht_HSDPA_Retainability.range('I2:I25').value
sht_master.range('AM2').options(transpose=True).value = HSDPA_Retainability_values
The "value" from the source work sheet range H2: H25; I2:I25 are all in formula therefore, please help me what is the code to copy paste "value" to the AN2 because copying it will result to "0" if not copy paste in value.
@@noeldoller2958 Thank you for watching the video your comment, Noel Doller.
Regarding your questions, you could add more parameters to the 'paste' function. Example to paste only values:
sht_master.range("A1").paste(paste='values')
Complete documentation can be found here:
docs.xlwings.org/en/stable/api.html#xlwings.Range.paste
I hope this helps!
@@CodingIsFun Thank you so much Sir for your guidance . it works fine now. I used the: range(A2).end(right).offset(0,1).options(transpose=True).value = HSDPA_Retainability_values
Much appreciated Sir
Thanks for sharing.
Any course that u recommended to take , to start of with python for Accountants.
Thanks for watching. You can find my personal recommendations here:
pythonandvba.com/resources/
If I create an automated excel and send it to a person Z, so I just need to send ".xlxm file" ? while Z does not have xlwings installed?
Yes, Z also needs to have Python (& xlwings) installed.
Hi brother Can you help me Out?! As I want to create a data Entry Form Sheet from which data is saved of different different columns and on multiple sheets. Please guide me
Have a look at my tutorial: th-cam.com/video/svcv8uub0D0/w-d-xo.html
It might be a good starting point. Happy Coding!
Nice! Super useful
Hi Florian. Thanks for watching the video. Glad you liked it! 😀
Hi. Can you show us how to create pivot tables? Thank you
Thank you for watching the video & your suggestion.
Hi Sven
How i Can refresh juste one single sheet in a workbook with several sheets . I am beginner . And for now i use pandas and openpyxl ?
Thanks for watching the video. What exactly would you like to refresh? A pivot table?
@@CodingIsFun thank you Sven for your quik answer . IS not a pivot table , looking for code == refresh in excel . (!= Not refresh all )
@@rachidbaha6400 Ok, but what would like to refresh then? Please give me an example. Thanks!
@@CodingIsFun thank you very much . I am on leave for two weeks , ill try to have Access to files and give you an example . we are five persons to do the burring staff To do the Boring stuff full time
Thank you!
A pleasure! Thanks for watching the video :)
Hai sir, ur video super. I learn so much ur channel. I have one doubt I'm using excel data python. Excel sheet cells I have created formula. Excel read data python program only copy formula not value. Please help me I need excel rows read values only. Please advise sir.
I am unsure what you are trying to achieve, but you might want to use openpyxl.
Please find below an example:
from pathlib import Path
import openpyxl
excel_file = Path(r"C:\Users\UserName\Desktop\data.xlsx")
wb = openpyxl.load_workbook(excel_file, data_only=True)
Happy Coding!
hi Sven, 2:57 why the column sales need to be written as df[" Sales"] instead of df["Sales"] - with and without space in front of the word Sales. What's the difference? 🤓
Thanks for watching. Because in the Excel file, the header name includes an empty space " Sales"
Hi Sir,
Great video.
Sir I tried using the same method but if the sheet has some blanks columns in between then how can i copy paste all the data in the sheet?
Like how can we perform a Ctrl+A in xlwings
@@omsangwan6167 Do you really want to copy all cells? It might be better to mention the cell range explicitly, like Range('A1:Z100')
otherwise, I suggest to use 'used range':
docs.xlwings.org/en/stable/api.html?highlight=used%20range#xlwings.Sheet.used_range
@@CodingIsFun sir actually i am automating the process for like 20 sheets so i cannot write it manually
The used_range option works for now
Thanks a lot
@@omsangwan6167 Sure, you can also iterate over 20 sheets.
How can I automate an excel file which can give me daily report? Right now I download an excel file then I use vlook up to find 7 KPIs for a call center. Can you guide, sir
Thanks for watching the video & your question. Without knowing your exact use-case, you could try to host your Python script, which performs the vlookup, on Pythonanywhere (www.pythonanywhere.com/). With pandas, you can also read in Excel files, which are stored on a web server. I hope this helps!
@@CodingIsFun thanks for your reply.
Can you show some automation stuff on excel through which a daily report can be automated. Like in macros - we just update the data and everything comes as output.
I have a sample report - would it be possible for you to make a video on it? I am from call center and we have lot of reporting stuff which takes lot of time.
@@ketuscorpion3717 Feel free to send me your example files via mail to: contact@pythonandvba.com
I will see what I can do, *BUT* I cannot make any promises. I hope you can understand. Thank you!
And yess please also knows how we can do sedual run of our file daily basis with help of python . Right now i m doing with VBA and window task sechualler.
@@sachindubey4315 Here is a good tutorial on how to schedule a Python script: th-cam.com/video/aqnJvXOIr6g/w-d-xo.html
I hope this helps! :)
Great learning video 🙏🏿
Glad you liked the video. Thanks for watching & your feedback :)
can you solve that problem for me?
df = df.append(pd.read_excel(file), ignore_index = True)
/Users/vplap73/Documents/excel/a.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
Thanks for watching and the hint. I went ahead and updated the repo: github.com/Sven-Bo/automate-excel-with-python#changes-after-releasing-the-video
Videos is good bt you skip last part please make some videos to explain VBa automaion tasks with python .
Thanks for watching the video & your comment. What exactly do you mean by VBA automation tasks with Python? Can you perhaps elaborate on what it is you're actually trying to achieve?
Please provide some additional info. Thanks!
Please make VBA based automation video.
Thanks for watching the video & your suggestion :)
How to automate large excel file in python?
It's a good question but such a difficult one to answer because it depends on so many factors.
how to import a graph from excel to word
Have a look here: th-cam.com/video/T3meVMaV8AA/w-d-xo.html
Hello There
Hello 👋
Hi, I sent you a mail. can you please reply to me. thanks
Hi Yogi. Thanks for your mail - I have just replied.