Search and compile multiple spreadsheets

**RoyLittle0** · 07-06-2013, 09:11 AM

Hi all

OK, this might take a little explaining what I want to do, but here goes.

The company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.

We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-
Serial Number
Machine Type
Hours Run Time
Reason for Visit
Reported Problem
etc, etc
All the forms are identical so all the fields are identical

Field Service Record Spares 2013 Master Rev4 (2).xlsx (sorry wrong file uploaded, this is the correct file)
The forms are then saved on a central server folder and stored in month order.

Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and past all the information into the master sheet??

Not sure if this is all a little too much for excel, or is there a better way of achieving this.

We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.

**SteelReyn** · 07-06-2013, 10:25 AM

You could set this up with some VBA, so that a "master sheet" opens each file one at a time, gets the information and puts it in some sort of table, and then closes the file and moves on to the next. As an example, we did this at work where the master sheet would add up values from every other workbook in the same file folder (each of them having an identical layout).

The tricky part, looking at your workbook, would be in figuring out how best to set up your data like rows in a table, which would make searching for information much easier. That might not be too hard, since you are familiar with the information, but since you are searching by serial number or fault, would it still be helpful to just get each file's serial numbers, faults, and name/location of the files and store those in a table? You could search for a serial number that way, and it would show you the file name needed to get more information.

Excel is certainly capable of rolling up all of your information, but how you intend to use the solution makes a difference in how much time and VBA you'd have to invest to make it work. Where I work, we tend to "portalize" this type of thing with a SharePoint solution.

**RoyLittle0** · 07-06-2013, 10:37 AM

Originally Posted by SteelReyn

You could set this up with some VBA, so that a "master sheet" opens each file one at a time, gets the information and puts it in some sort of table, and then closes the file and moves on to the next. As an example, we did this at work where the master sheet would add up values from every other workbook in the same file folder (each of them having an identical layout).

The tricky part, looking at your workbook, would be in figuring out how best to set up your data like rows in a table, which would make searching for information much easier. That might not be too hard, since you are familiar with the information, but since you are searching by serial number or fault, would it still be helpful to just get each file's serial numbers, faults, and name/location of the files and store those in a table? You could search for a serial number that way, and it would show you the file name needed to get more information.

Excel is certainly capable of rolling up all of your information, but how you intend to use the solution makes a difference in how much time and VBA you'd have to invest to make it work. Where I work, we tend to "portalize" this type of thing with a SharePoint solution.

Thanks for the reply.

I would be quite happy if I could set up a master Excel Sheet to extract the Serial Number, Date, Reason For Visit and Reported Problem, I could limit each to one line, therefore I would only be extracting a overview of the problem and cure, it would also be nice if I could include a link to the actual file, if that is possible, I could even add a Fault category that would be a selectable value.

This is something i have never done before so don't really know where to start looking for the answer

**SteelReyn** · 07-06-2013, 04:50 PM

Before being able to give much specific help, I would want to refresh my memory by looking at the example I told you about above (no good til Monday). I'm not a VBA pro, so specific code does not come to mind.

If you wanted to start experimenting, there are lots of examples to be found with Google searches for each piece of the puzzle. I'd start by finding out how to locate files within the same folder, open a file, get data from one workbook and move it to another, close a file, and loop through the process until all files have been processed. Keep breaking the problem down to smaller bits and it will be much easier.

That's still a tall order if you're not familiar with coding, but I'm sure we can help as you get things started. Maybe someone here with a better VBA background can give some advice?

**RoyLittle0** · 07-07-2013, 04:38 AM

Thanks for your help SteelReyn

I am not that familiar with VBA but in my younger years I have written a few programs in VB using Microsoft Visual Studio, so I think I will be able to get my head around it with a little guidance, I will do some digging today and tomorrow and see what I can come up with, now I know it can be done I can work on the programming as and when I get time.

Roy

**RoyLittle0** · 07-07-2013, 05:43 AM

OK so I think I have found the code I need to open and close the files in my folder

The master document is FSR Master.xlsx and I wont to be able to run the VBA on this document to get the data, the Master document also contains the cell location of the cells I want to copy, can I also get the file location as a hyperlink?

Please Login or Register  to view this content.

**SteelReyn** · 07-07-2013, 04:15 PM

If you're already getting the file, adding a hyperlink is pretty easy. Take a look at the code below, copied from the Microsoft Excel help:

Please Login or Register  to view this content.

Most of that is probably self-explanatory; the anchor is the cell where you want the link, the address will be the file path, and the text will probably be either the file name or path in your case. Shouldn't need the screen tip. I tested using a file on my computer instead of the MS site and the link opens the file as expected.