Thank you. Good job! :) Thanks for the content. With love, from South Africa.
@CodingIsFun
3 жыл бұрын
Thank you for watching the video & your comment. Greetings to South Africa :)
@asankacool1
3 жыл бұрын
Nice video flow design. Useful stuff…!
@CodingIsFun
3 жыл бұрын
Thank you!
@sayantandatta2996
2 жыл бұрын
u are amazing kudos to your effort❤👍 thanks
@CodingIsFun
2 жыл бұрын
Thanks for watching the video & your comment! 👍
@K-mk6pc
Жыл бұрын
Good Insights.
@CodingIsFun
Жыл бұрын
Glad you liked it. Thanks for watching. :)
@JUN1ORLYONN
3 жыл бұрын
Great video!
@CodingIsFun
3 жыл бұрын
*I am glad you liked the video; thanks for watching and for the comment!*
@Abramov_Andrey
Жыл бұрын
Отличный пример! спасибо!
@CodingIsFun
Жыл бұрын
My pleasure! Appreciate you taking the time to watch and leave a comment.
@ahmadmojeed5507
2 жыл бұрын
Great tutorial again! 👌 Really helpful but in my case, i would like to create a new sheet for each excel files but sadly, it throws error. Example, wb = load_workbook(filename=excel_files) wb.create_sheet("test") Then,i get a TypeError : expected str... not list
@ahmadmojeed5507
2 жыл бұрын
Would love to have same sheet in all excel files so i can add a drop-down in my master sheet.
@CodingIsFun
2 жыл бұрын
I am not entirely sure what you are trying to achieve, but 'excel_files' is a list of file paths. That is why you are getting an error.
@PiotrBotor-vo9yo
14 күн бұрын
Hi ! One question. When You are saving master file as a new file, what is happening with sheets, scripts, macros and other references which was previousy inside master file ? Is IT copied without changing it or beeing lost?
@CodingIsFun
14 күн бұрын
Hi. Why not simply trying it out? :) Currently, only the values are copied. -Sven ✌️
@tsiddiq23
3 жыл бұрын
Great work
@CodingIsFun
3 жыл бұрын
Thank you! 😀
@royalwinautomatedsoftware2985
2 жыл бұрын
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...
@CodingIsFun
2 жыл бұрын
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!
@aquabestindian
2 жыл бұрын
Thanks for the tutorial. One problem, the data is not stored as values but are formulas, and the solution in the video is not fetching values ! How to modify the code to get values?
@CodingIsFun
Жыл бұрын
Thank you for watching the video. Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server ( pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
@mikekaspari1357
3 жыл бұрын
Hi Sven, danke fürs Video. Ist es möglich die Rows und Columns im Mastertemplate zu drehen, also das Datum entlang der Colums und die Produkte in A bis nach unten. Aber versucht den Code umzuschreiben, ging aber nicht
@sachinmahale6003
2 жыл бұрын
This is really a great tutorial Sir. I am new in this sector. I am trying to merge multiple excel files into one master excel file. The code runs well using pandas, but it removes all the formatting from master excel file. I saw this video and found that the formatting in the master excel file remains as it is. Is it because of the openpyxl package instead of pandas?
@CodingIsFun
2 жыл бұрын
Thanks for watching the video and your question. Yes, that is correct. In fact, pandas is also using openpyxl under the hood. Yet, when creating a pandas dataframe, pandas will remove the formatting. If you use openpyxl directly, you could keep the current format of the excel file. Hope this clarifies. Happy Coding!
@sachinmahale6003
2 жыл бұрын
@@CodingIsFun Thank you very much Sir for clarifying my doubt. This helps me a lot.
@marcelduplessis
2 жыл бұрын
This may be a silly question, but I'm trying to add data from a excel file into a template without the date comparison. The idea is to grab everything from A2:K99. I can't seem to get this going :) How can I modify this script to do this?
@CodingIsFun
2 жыл бұрын
Thanks for watching. How about using 'xlwings' for this? Here is an example for you: import xlwings as xw #pip install xlwings source_wb = xw.Book('Source.xlsx') target_wb = xw.Book('Target.xlsx') sht_source = source_wb.sheets['Sheet1'] sht_source.range('A2:K99').copy() sht_target = target_wb.sheets['Sheet1'] sht_target.range('A2').paste() I hope this helps! :)
@noeldoller2958
3 жыл бұрын
Great tutorial Sir. I subscriber to your channel as there a lot of tutorial that would help me in my work. Sir Im just started to learn python and I badly needed to learn because my work requires a lot of automation in excel. Appreciate sir if you can help me for one basic example. Need to automate copy and pasting range of cells from one multiple work book to Master excel file. Can you help me with to code copying range of data from column B2:B28 of sheet name "raw" of excel file name "paging" to paste in master excel file sheet name "CSSR" column AL2:AL28 and next day same source file to the next column AM2:MA28 and AN2:AN28 and soon. Thank you sir waiting for your help
@CodingIsFun
3 жыл бұрын
Thanks for watching the video. *Here is the code snippet:* import xlwings as xw #pip install xlwings wb_source = xw.Book('paging.xlsx') wb_master = xw.Book('master.xlsx') sht_source = wb_source.sheets['raw'] sht_master = wb_master.sheets['CSSR'] source_values = sht_source.range('B2:B28').value sht_master.range('AL2').options(transpose=True).value = source_values I left out the column change for each day (AM, AN, AO, ...) as this might be a good learning project for you :) Hope this helps & happy coding!
@noeldoller2958
2 жыл бұрын
@@CodingIsFun Much appreciate sir. How can i support you sir in return
@CodingIsFun
2 жыл бұрын
@@noeldoller2958 *Liking the videos is the best support.* ❤
@noeldoller2958
2 жыл бұрын
@@CodingIsFunYes sir, all your videos are worth liking and I already liked them all as I already compared it with similar videos from other programming experts. In fact Im always looking for your new videos. The latest I watched and liked was the Pandas short cut
@CodingIsFun
2 жыл бұрын
@@noeldoller2958 Thank you for your support! 🦾
@gregorydubois8468
Жыл бұрын
Hi Sven, each of the files I need to combine into one workbook has a date in its title. I would like to get this title and copy it into a column I will create in each file so that at the end, I have all the rows with the titles from the files they have been copied from. How can I do it? Many thanks!
@CodingIsFun
Жыл бұрын
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!
@gregorydubois8468
Жыл бұрын
I totally understand, do not worry :-) I will dig into the net and try to find out something. I love your channel!
@Yoitsryn
Жыл бұрын
What if the worksheets have different names?
@CodingIsFun
Жыл бұрын
Then you need to change the code ;)
@rosalyna_24
2 жыл бұрын
but if i want to do the opposite , dividing the data from the master workbook to multiple excel file how should i proceed ??
@CodingIsFun
2 жыл бұрын
That is also possible. Check out the following videos: kzitem.info/news/bejne/1J5u1nmojGRipXo kzitem.info/news/bejne/r416mICJjKeqnXo Happy Coding!
@rosalyna_24
2 жыл бұрын
@@CodingIsFun thank you so much
@akshayathalye264
Жыл бұрын
How can we make the code work if the range within each workbook changes?
@CodingIsFun
Жыл бұрын
Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!
@tanganhminh9138
Жыл бұрын
1. The interface to write python code, I see it seems to be localhost8888. but when i access it it is blocked, is there any way to solve this problem? 2. Extract all info of 1 video or 1 playlist or all videos of 1 youtube channel into excel file, use python can work ?
@CodingIsFun
Жыл бұрын
1. I am using a Jupyter Notebook. Set up guide can be found on my channel 2. Yes, it's possible, but I cannot explain all the steps in a single comment. Happy Coding!
@tanganhminh9138
Жыл бұрын
@@CodingIsFun 1. this video, right ? kzitem.info/news/bejne/uXmsza2se3ibrII
@CodingIsFun
Жыл бұрын
@@tanganhminh9138 yes
@tanganhminh9138
Жыл бұрын
@@CodingIsFun 2. if you have time, hope you will do the above problem, ok?
@CodingIsFun
Жыл бұрын
@@tanganhminh9138 you can join our Discord and ask for help there. Link is in the description
@andrewwithers4971
Жыл бұрын
Hi, I get the following error SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape Any idea how to get round this ?
@CodingIsFun
Жыл бұрын
Which line is causing the error and did you modify the code from the tutorial?
@andrewwithers4971
Жыл бұрын
@@CodingIsFun I managed to fix the error. the code seems to work now however the new file "Masterfile_Filled" is empty any ideas why this could be ?
@CodingIsFun
Жыл бұрын
@@andrewwithers4971 Hard to tell from a distance
@andrewwithers4971
Жыл бұрын
@@CodingIsFun I now have the code working how I need it however I would like use different file names. is there a way to take the file names in the directory and add them to column "B" ? I tried this - from pathlib import Path # Standard Python Module from openpyxl import load_workbook, Workbook # pip install openpyxl # -- 1.STEP # Get all excel file paths from given directory SOURCE_DIR = "excel sheets" # e.g. r"C:\Users\Username\Desktop\Sample Files" excel_files = list(Path(SOURCE_DIR).glob("*.xlsx")) # -- 2.STEP: # Iterate over all Excel files from step 1, # access the worksheet and store the values in a dictionary # values_excel_files = {['Project Costs File 1'] : [1,2,3, ..], # ['Project Costs File 2'] : [1,2,3, ..]} values_excel_files = {} for excel_file in excel_files: report_date = excel_file.stem.replace("_Report", "") #** This was the replace file name wb = load_workbook(filename=excel_file, read_only=True) #print(report_date) #######select cells from sheet rng1 = wb["Sheet1"]["B2":"B2"] rng2 = wb["Sheet1"]["B4":"B4"] rng3= wb["Sheet1"]["B6":"B6"] rng4=wb["Sheet1"]["B6":"B6"] rng =rng1+rng2+rng3+rng4 rng_values = [] for cells in rng: for cell in cells: rng_values.append(cell.value) values_excel_files[report_date] = rng_values # -- 3.STEP: # a) Iterate over all worksheets in the master workbook # b) For each worksheet, iterate over defined Excel range (dates) # c) If date matches with the key of dictionary (values_excel_files) then insert values & save workbook wb = load_workbook(filename="Masterfile_Template.xlsx") for ws in wb.worksheets: ws.write(B3, report_date)
@CodingIsFun
Жыл бұрын
@@andrewwithers4971 Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server (pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
@youngking7849
2 жыл бұрын
hi can u help me
@CodingIsFun
2 жыл бұрын
Hi. With what?
@ohmandy09
Жыл бұрын
Great video thanks for sharing, I just found this channel, do you happen to have a video on how to copy from multiple .xlsx source files just like yours but paste in multiple individual destination .xlsm files that are all in individual sub folders eg copy data from 20210101_Report.xlsx and paste in existing file 20210101_Sales.xlsm, copy data from 20210102_Report.xlsx and paste in existing file 20210102_Sales.xlsm where is 20210101_Sales.xslm is in a sub folder 'C:/folderpath/Jan1/' and 20210102_Sales.xlsm is in another sub folder 'C:/folderpath/Jan2/'
@CodingIsFun
Жыл бұрын
Thank you for watching the video. Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server ( pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
@ohmandy09
Жыл бұрын
@@CodingIsFun Thank You! I figured it out. Your video got me 1/4th of the way there, your discord server got me 3/4 of the way there, google and stack got me the rest of the way. I just bought you coffee please keep up the good work, my Thank You is all the way from Atlanta Georgia USA.
Пікірлер: 64