Handling large data across small company

[storm8]

Hello,

I use excel for all our data manipulation (web visitors etc...). I have table with about 40 columns and 330.000 rows. The data are output of complex macros and are then exported to separate .xlsx file on network drive. Each user has own excel file with custom pivot tables and imports new data from network .xlsx to their own file (they are hard copied into each file), then the pivots are updated...

However this is obviously not a good solution. Since pivot tables cannot be updated from other excel file that isnt open, I cannot really decide which is best.

I am considering exporting data to access and then using connection somehow, but have no experience with it, I also tested excel connection but was not able to locate anything within the source file with the wizzard. Also I might use MySQL.

Can someone help me with this one please? Thanks alot...

 

[Gary McMaster]

Maybe something you can use here:

http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

Gary

 

[storm8]

Thank you! Finally I was able to get the data form other .xlsx file via odbc (I think), however it still has to be copied to each's hard drive since it cannot be accessed by multiple users simultaneously (if on network drive). Now I have marco that checks whether file size of network file differs from size of file that was last used for importing data and if yes, it will download a copy and then update the pivot tables.

Any suggestions for improvement?