Author Topic: Question for Excel Macro wizards  (Read 918 times)

Manguy888

  • Stubble
  • **
  • Posts: 248
  • Location: Rhode Island
    • EA Mann, Writer
Question for Excel Macro wizards
« on: May 23, 2017, 09:28:19 AM »
Hey everyone - I just want to know if something is possible with excel macros.

I have a top-level folder on a shared server where a bunch of people's technical work happens. Each piece of work has an excel spreadsheet where we collect data, and one of the tabs is a "daily status" tab to track their effort.

Would it be possible to have a macro traverse the folder structure, look inside and xls files, grab any excel tabs with the name "daily status" and copy them to an "All Status" excel file at the root of the folder structure?

Use case would be I get in in the morning, open the "all status" file, hit an update button, and all of the status tabs for the dozen+ tasks are copied into this file. If my upper management wanted to see where any work was at, they could go to this one place.

Not asking anyone to figure this out for me, just wondering if it's feasible. If so I will dive in and learn excel macros

ShoulderThingThatGoesUp

  • Magnum Stache
  • ******
  • Posts: 3057
  • Location: Emmaus, PA
Re: Question for Excel Macro wizards
« Reply #1 on: May 23, 2017, 10:38:55 AM »
Yes, that's absolutely possible.

But it would be better if everybody updates their daily status in a Sharepount list with a nice form or something like that. Your data-gatherer is going to spend a lot of time opening files to get just a little but if information.

plog

  • Stubble
  • **
  • Posts: 211
Re: Question for Excel Macro wizards
« Reply #2 on: May 23, 2017, 10:58:58 AM »
Yes it's possible, but I don't think its the right tool.  It might be time for some off the shelf project management software, or for you to go to your IT department and request a bid for a custom tool.

From a selfish standpoint, I'm all for convoluted, hacked-together, overly-complicated Excel systems that only the person who built them can understand and fix--I've made a nice living coming in after those people and moving data to proper platform.  But, I always advise against it before the system starts to take hold, because something like this will need to be supported.  Not 24/7 but it will need to be fixed/updated at some point:

What happens if you move servers/directories?
Or move to .xlsx files?
What happens if people change the tab names?  Or misspells them?
What happens when you want to start running more complicated reports?
And the big one:  What happens when the Excel guru leaves the company?
« Last Edit: May 23, 2017, 11:02:55 AM by plog »

Manguy888

  • Stubble
  • **
  • Posts: 248
  • Location: Rhode Island
    • EA Mann, Writer
Re: Question for Excel Macro wizards
« Reply #3 on: May 23, 2017, 11:41:23 AM »
Yes, that's absolutely possible.

But
Yes it's possible, but

haha - I've been on the internet long enough to expect this type of response. You're right, obviously. Suffice to say though that I work for a big project and their culture is resistant to change. And in my zen money mustache state I feel no need to fix it; I used to get all worked up about that kind of stuff, but no more. If my convoluted solution makes me look like an irreplaceable wizard to the dinosaurs I work with, so much the better.