So I had tried using the data import wizard or whatever it was that’s built in to pull an entire folder into the workbook, but I had roughly 70 workbooks, all with 40 columns and anywhere from 3,000 to 20,000 rows. At the end of it all, I probably had over 20 million cells. The built-in tool was being finicky. I think it was that the sheer amount of data I was working with was too much for it to handle. But it kept giving me errors about formatting.
So I gave up on it, and I spent several hours of my life reading manuals and forum posts on how best to achieve one step and testing code on backups. It was truly an all-day thing. But when you’re dealing with dozens of files, this macro takes maybe 45 seconds to do its thing whereas the manual process could waste an hour of your day. And I plan to share it with the team once I get it a bit more polished because it’s not exactly where I want it. But I think the rest of my colleagues will love it.
I know a little VBA. I spent a whole weekend writing a macro because I did my personal budgets in Excel, and I wanted to automate some stuff because I could conceptualize how it could be done. I don’t use Windows at home anymore so I want to figure out how to bring it over to LibreOffice Basic. Still, since the business world uses Microsoft products, knowing VBA is a much more marketable skill so it is useful to practice in VBA whenever I can.
What’s the advantage of VBA in this use case over other options?
They said they barely know vba. So it’s probably the integration into MS tools.
So I had tried using the data import wizard or whatever it was that’s built in to pull an entire folder into the workbook, but I had roughly 70 workbooks, all with 40 columns and anywhere from 3,000 to 20,000 rows. At the end of it all, I probably had over 20 million cells. The built-in tool was being finicky. I think it was that the sheer amount of data I was working with was too much for it to handle. But it kept giving me errors about formatting.
So I gave up on it, and I spent several hours of my life reading manuals and forum posts on how best to achieve one step and testing code on backups. It was truly an all-day thing. But when you’re dealing with dozens of files, this macro takes maybe 45 seconds to do its thing whereas the manual process could waste an hour of your day. And I plan to share it with the team once I get it a bit more polished because it’s not exactly where I want it. But I think the rest of my colleagues will love it.
I know a little VBA. I spent a whole weekend writing a macro because I did my personal budgets in Excel, and I wanted to automate some stuff because I could conceptualize how it could be done. I don’t use Windows at home anymore so I want to figure out how to bring it over to LibreOffice Basic. Still, since the business world uses Microsoft products, knowing VBA is a much more marketable skill so it is useful to practice in VBA whenever I can.