Hi
I'm sure this must be possible with macros but it's beyond my limited skills!
In a master spreadsheet I have a list of paths to Excel spreadsheets that I want to open, extract data from, plot in the master spreadsheet, close and move on to the next. How do I go about this?!
Thanks

Can be done using vba script. Quite complicated to write without having the file names paths of worksheet layout infront of you (well with my level of knowledge it is)

If you have both spreadsheets open you can create a graph in one that looks at the data in the other.

However this is a recipe for a very slow running spreadsheet.

Similarly you could create a pivottable in one that looks at the other spreadsheet and then plot the graph from that.

Hats off to the person who can summarise the vba programming that you're going to need.

The first thing you need to do is create a sheet in the consolidating workbook that contains all the filepaths for the sheets you want the macro to look to.

I'm glad it sounds complicated, it's virtually the first thing I've been ask to do! As a start how do I get VB to go down my list of paths and open the spreadsheets? I know it's to do with counting and loops!

to do the loop, simplest is a for next loop

for i =1 to 100
offset (0,-1)
if worksheet("wibble").cell("wobble")=10 then end else open a spreadsheet
next i

One way i find that helps is open the spreadsheets, and then do what you want whilst recording a macro. if you then open the macro in the VBA window, you should be able to figure out how it works and how to tweak it to do what you want.

not right but gives an idea of how you could do it.

you could use select case, while wend, etc.

in a effort to work through what you want.

Open c:/whatever
do a loop to find what you want
once you find it copy to another workbook
close c:/whatever
open the next

etc

Mrmo, yep I've got a recorded macro that does the plotting of the data, but only in the opened spreadsheet not in a master. So I'm thinking I need some sort of wrapper that'll open these spreadsheets to bring the bits of data into the master. Someone has written a macro to get the paths to all of the spreadsheets I'll be needed. I think I need a loop here as the paths it finds will change day to day i.e. I can't just record me doing it once and use it again.

Open the master spreadsheet, start recording, open all the other spreadsheets one at a time extracting data and then closing them. That will give you hard coded start point. If the list of spreadsheets will vary or the data being imported needs appending rather than just replacing the existing data it's going to get a lot more involved.

Tis abit difficult to explain on here but it can be done fairly easily with VBA.

You need to do something like this where you search for the files you want to open. These file references get stored in a foundfiles object. You then loop through the object to open each file and, making sure the sheet is active copy the data across to your master, before closing it and moving on to the next one.
Rather than search for '*.xls' files it may be better to use a mask to make sure you only open files required.
It may also be best, if the files are not going to be in the same place each time, for the vba to refer to one fixed directory and just copy the files into place when required.

Sub App_FileSearch_Example()

Dim OWB As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "c:\vbadud\delivered_files\"
.FileName = "*.xls"

If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) > 0 Then

For i1 = 1 To .FoundFiles.Count
Set OWB = Workbooks.Open(.FoundFiles(i1))
'Add copy routine here
OWB.Close
Next i1

End If

End With

End Sub

ps. Someone gissa job! I love doing this sort of stuff but no-one wants me. :0(

Thanks everyone