DataFrameName = xl("cell range", headers=True/False) should exclude the header row. This can be used in second and third data-frames. eg. df2 = xl(OtherYear!A1:I293,headers=False)
There have been heated discussions with the developers on this point. To actually exclude the headers, I would have to start the range at A2. This seems to annoy a lot of people. Using Headers=False makes sure they are part of the data and not treated as headers. Sort of like pressing Ctrl+T and choosing Headers=False.
@@MrXL Thanks for letting me know. I just assumed it will work without testing and allowing for the fact that it is still in Beta. Your solution is elegant as usual.
You can do this : df = xl("Table_name", headers=True) df.values will give you the data without the headers. the option headers=(True|False|None) doesn't seem to remove the headers.
This is awesome! Just experimenting I wanted to see if there was a way to dynamically get the ranges for the data frames. I created two dynamic na.ed ranges using the OFFSET function then put the names in the python expression instead of the cell range and it worked!! New data was automatically added to the named range and to the concatenated data frame.
If you are referring to a range (not table) then it will only update if you "insert" the row "in" the range. For example, xl("Sheet1!A1:C4", headers=True) will be updated to xl("Sheet1!A1:C5", headers=True) in this case. However, adding a new row to the next available row won't trigger the range update in the PY formula. However, if you used a table to create the dataframe then it will work even if you added a new row. Basically, it works just like any other formula in Excel.
Hi, Thank you very much for this time warp :). Is there a way to access those Python objects from VBA? (e.g. data frames, or even using Python functions) Or on the other hand, accessing VBA from Python?
These Python videos would be more useful if they came out after the roll out to us regular users. A little frustrating seeing this without being able to try.
Really easy ti sign up for Beta channel. If you can't at work then 365 home subscription quite cheap. Besides these videos will stll be around whenever you do get access to Excel Python.
This is great for data within the same workbook but how do you consolidate data from external workbooks ? I tried the pandas read_csv() or read_excel() functions but failed. Apparently, Python+Excel does not seem to recognize the path + file combination. I tried both options in native Python and it works perfectly.
It’s a huge limitation. Python in Excel can’t read from anything external to the workbook. I can’t get any image compare code to work because I can’t get the images loaded. I am creating a new video on abandoning Python in Excel and moving to a local install of Python.
@@MrXL Thanks for your reply. I tried several versions of the xl() function but it works only for workbooks already loaded. Re: your video, the local version of Python is working very well for consolidating multiple files from various sources (Excel, csv, json, etc.). The code is simple and very readable. On top of this, the Jupyterlab environment is very good for documenting the code. Thanks for your videos. Eager to see your work on Python !
Of course not on Mac yet :) It is a little bit disappointing, because it doesn't even require a local third party installation and as far as I understand, the function is simply interpreted on a Microsoft cloud server. So, I don't understand why they didn't include this update in beta on Mac, but it is what it is. If you are interested testing it without having it running in your Excel, you might want to check my detailed reply to @RonDavidowicz for the Jupyter Notebook workaround. It is not the exact same thing but very close to understand and learn it before it arrives to Mac.
We all know that the Mac is a poisonous machine that should be placed on a rocket and fired at the sun anyway, so that's probably why Microsoft didn't offer this for such users.
With all due respect Mr.E, it makes NO sense to Zip a single Excel file. If you're concerned about file size, saving as an XLSB is even smaller than today's Zip file and still supports Macros. A Zip file makes sense for multiple files, and maybe that's just how you're set up, but otherwise zipping a single Excel file is just a hassle - repeated the hundreds or thousands of times the file is downloaded.
Point taken. The web utility for creating the page was built with the thought that I would be including multiple workbooks. And, by using a zip file, the zip file name can match the RU-vid Video ID which will help with database management. For 16 years, I refused to post the workbook because it was too much of a hassle. I finally paid to have a utility made to build the web page and have a place to hold the workbooks. For now, the files will keep getting zipped because that's how the system is built.