Ideas on how to get data from my excel sheet into access or better ideas?

[tonywatsonhelp]

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

 

[Smitty]

Do you have access to Power Query? Or you might be able to consolidate to a .csv file, then use Power Pivot for analysis, but not even SQL Server is going to let you have 3,000 columns.

But why do you have 3,000 columns? That's going to be your biggest problem. And if each store is filling out 3,000 columns of data on a weekly basis, then I think your process needs some serious re-evaluation.

Since you have a network, then I'd consider moving your data collection to Access. It's not hard to create front & back-ends then use the Package & Deployment wizard to create stand-alone .exe versions for your stores (which means they don't need copies of Access).