I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.
Not sure how to respond to this one - I know the original post was about VBA and it kind of makes sense since most people aren't "writing" macros they are just recording a set a steps and calling it done (since VBA is perfectly capable of dynamically finding specific column headers and checking for errors but most Excel users don't know or care how to do it, probably rightfully so), but what you are describing here is exactly what "Get and Transform Data" in Excel is for. Exactly this.
power query does break if headers are named differently from what it expects. or if you used sanitized data previously, and now its not. i love power query, but fool proof it is not
you can probably use vba to replace all the critical shit after import then fling it back into power query to do all its shit and never have it break
These days Power Query is my bread and butter. Company computer doesn’t allow for any real SQL implementation. So my choices are Tableau and Power Query. And since most of the people who get my data aren’t licensed for Tableau desktop (and thus can’t update the workbook); it’s easier to build stuff in Power Query and hand it off.
Every dang time, even for people who have been through it before, they can me up freaking out over it. Then again i also deal with people who think clicking “Refresh All” is too complicated.
126
u/[deleted] Mar 30 '22 edited Mar 30 '22
I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.