tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,212
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi everyone,
Here's my dilemma,
I have 17 Stores, each store has there only Excel Spreadsheet to record weekly sales (I use as an indicator as our bosses only do accounts once a year and I need to know every week how we are doing)
Every store has an Identical Document and the set up works great as we are only a small business and I need an easy cheap way of getting this information.
Any so every week I have 17 documents on my server that I have to go to to get the sales reports etc.
I tried to create an excel macro to pull the data in for me but because of the way they are set up I have to Open every Document and copy the data and even with a macro this is taking time but the biggest problem is the file size,
I collect so much data that every Documents info Is on one sheet from Column A1 to Column CAT344, so this is very slow pulling this all in and make the file size huge, so I'm looking for a suggestion on what to do now?
any ideas how to get this data from 17 file into 17 sheets in my reports document without taking ten / 20 minutes?
If it helps the data is all on my server the server drive is S but I can edit those if you assume it normal location.
every file is the same sheet (Shhet Data") and the file names locations will be the same.
I'm looking for ideas so anything I might not have tried would be great, I tried using the Get data wizard and this would have worked great but would only bring in 244 ish column and I have over 3000.
Thanks
Tony
Here's my dilemma,
I have 17 Stores, each store has there only Excel Spreadsheet to record weekly sales (I use as an indicator as our bosses only do accounts once a year and I need to know every week how we are doing)
Every store has an Identical Document and the set up works great as we are only a small business and I need an easy cheap way of getting this information.
Any so every week I have 17 documents on my server that I have to go to to get the sales reports etc.
I tried to create an excel macro to pull the data in for me but because of the way they are set up I have to Open every Document and copy the data and even with a macro this is taking time but the biggest problem is the file size,
I collect so much data that every Documents info Is on one sheet from Column A1 to Column CAT344, so this is very slow pulling this all in and make the file size huge, so I'm looking for a suggestion on what to do now?
any ideas how to get this data from 17 file into 17 sheets in my reports document without taking ten / 20 minutes?
If it helps the data is all on my server the server drive is S but I can edit those if you assume it normal location.
every file is the same sheet (Shhet Data") and the file names locations will be the same.
I'm looking for ideas so anything I might not have tried would be great, I tried using the Get data wizard and this would have worked great but would only bring in 244 ish column and I have over 3000.
Thanks
Tony