0

I have co-workers that keep track of product they work on using an Excel workbook, each person has their own. Currently if I want to find out who worked on an item, I have to search each workbook individually, or regularly compile them into one workbook.

I have tried making a master workbook using Connections and referencing each workbook into a sheet for each employee but apparently this has a limit of 255 columns.

I am looking for advise on the best way to create a workbook to combine all the info from my coworkers into one Master workbook. I have read about SSIS but have no idea how to go about creating/attempting this as I can NOT find the Excel Connections Manager that all my research talks about.

EX. When I open my master workbook, Steve, Jess, & Kelley's individual workbooks automatically populate into mine so I can easily search all at once.

Any suggestions/ advise would be very much appreciated. Thanks you

Improve this question
4
  • I have Absolutely done that and I am not asking this question without having tried everything I can think of. I was concerned about receiving negative points by asking this but I do truly need help. I started using the Connections options in the DATA tab creating links to the workbooks I need referenced in the master workbook but this process is limited to only filling 255 columns of data.. I even attempted a VB script but was utterly confused by it then moved on to other possibilities such as SQL but can't figure out how to download to even try to figure it out on my own.
    – Kyle Hinkebein
    Commented Mar 19, 2015 at 19:59
  • The information and data base would have to stay local within the company on our servers.. Creating an SSIS or using SQL seems to be all my research leads to, I was hoping there was a simpler option or perhaps someone could explain how to get started. I keep hearing about Excel Connections Manager or Sources Manager but can not find it or figure out how to find/download it..
    – Kyle Hinkebein
    Commented Mar 19, 2015 at 20:19
  • SSIS is a technology for moving data but it's very inflexible when it comes to data types. Excel interprets the data every time a spreadsheet opens and can just as easily decide a column is text as it is numeric. That makes these two mortal enemies. The fact that you're bumping up against some 255 column import limit in Excel leads me to believe that this will be hydra slaying event.
    – billinkc
    Commented Mar 19, 2015 at 20:19
  • Ok, I appreciate it man. It sounds like vb may just have to be the solution.. I'm just going to have to start from scratch with simply vb functions to figure it out then.. I was trying to get this done soon as a surprise for the boss before someone else figured it out.. lol Not a lot of time to work on it at work.. Thanks again for the SSIS warning, I was going nuts trying to figure out how to attempt it, I guess I'll spare myself that madness..
    – Kyle Hinkebein
    Commented Mar 19, 2015 at 20:42

3 Answers 3

Reset to default
1

SSIS is a very powerful tool. I've used it successfully to consolidate data from thousands of spreadsheets into a database, catering to varying sheet and column contents. However it is a developer tool with a steep learning curve.

I would use the Power Query Excel Add-In for your challenge. It can combine data from multiple files into a single Excel table, e.g.

http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/

Improve this answer
1
  • I tried using the Power Query and I had the same problems with it only wanting to load a certain amount of my data instead of all of it, it's the same thing that happened when I tried to use data connections.. I appreciated the suggestion though..
    – Kyle Hinkebein
    Commented Mar 23, 2015 at 20:12
0

If all of the documents are saved on the network or on your local drive you can write some VBA to quickly import the data. Start with:

Workbooks.Open ("C:\thefilepath\thefile")

then you can copy the data into your 'master workbook' and automatically close the other sheets. You can also write some code that would let you select only the files you want open if that's what you are looking for.

I think this will prove to be a slow option if there is a lot of data. Database options other than excel will probably work better.

Improve this answer
1
  • I will give this a shot one more time. I was using Data Connection to link my workbooks to a master but they are limited to 255 columns.. I tried a vb script with similar function to what I needed but could NOT figure out how to teak it to my needs. Creating a SSIS data base/ link seems to be the most popular but they all say something like "go to the Connections Manager" or " Sources Manager" which I can not find, and I can't even find anything explaining how to find it.. The only thing I can find is the sources used for data connections in excel which again, will not work for me..
    – Kyle Hinkebein
    Commented Mar 19, 2015 at 19:59
0

Use VBA!

its very simple to learn, it will take about 30 lines of code to do this.

for each workbook

open...

copy all rows into source sheet

close
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .