How to extract data from one cell in several spreadsheets into one document.

QuestionEdit

Dear Tom - I am a newbie to VBA and could really do with some help.

I am trying to set up a master spreadsheet that extracts data from several different spreadsheets.

I currently have to complete reports for health and safety within different areas of a company. I create a newfile (from a saved template) for each report which has several sheets including a "Conclusion" sheet.

The conclusion sheet is where I record all the findings and at the bottom there is one cell where I put a short summary.

What I want to do is to extract the data (text) from this cell in the "Conclusion" sheet into a master document. I would like to be able to extract the same date from all the reports created (they all have the same format/layout into the master document. I would also like this process to be automatic so that when I create a new report (from the standard template) that this summary is automatically extracted into the master document.

Basically what I would like to do is to;

Extract data from one sheet in several work books to a master document with the facility of the master document automatically updating when a new report is created.

Any help will be greatly appreciated.

Many

AnswerEdit

Scott,

do you know VBA? I will assume you do since this is a peer to peer support forum where equals share knowledge.

You have described functionally what you want to do, but nothing specific.

You say automatically update, but there is no event associated with creating a new report that would trigger the macro to run. If you want something to be automatic, then you would need to use a formula. If you are willing to run a macro, then you could design a macro to loop through all workbooks in a certain location as an example

Sub ABC() Dim sPath as string, sName as String Dim bk as workbook, sh as worksheet, r as Range, s as String Dim iloc as Long, rw as Long sPath = "C:\MyReports\" sname = dir(sPath & "*.xlsx") rw = 20 do while sname <> ""

 set bk = workbooks.Open(sPath & sName)
 set sh = bk.worksheets("Conclusion")
 set r = sh.Range("F22")
 s = r.value
 thisworkbook.worksheets("Summary").Cells(rw,"C").Value = s
 iloc = instr(1,bk.Name,".",vbTextcompare)
 thisworkbook.worksheets("summary").Cells(rw,"B").Value = left(bk.name,iloc-1)
 rw = rw + 1
 bk.close Savechanges:=False
 sName = dir()

Loop End sub

so this code loops through all files with an .xlsx extension in the directory "C:\MyReports"

it opens each file, goes to cell F22 in sheet conclusion of that workbook and gets the text in that cell. It places that text in the workbook containing the code in sheet summary starting in row 20 and placed it in column C. In the same row in column B, it places the name of the workbook that provided the data with the .xlsx stripped off (assume this is the report name - you could of course get information from elsewhere in the workbook).

It then closed the workbook and moves on to the next one.

So as soon as a report is completed, you can run a macro like this to rebuild the summary section of your master workbook.

That is an example of code that might be useful to you and illustrates that specific knowledge is required to translate general concepts into code. Hopefully you can take that as a basis and adapt it to suit your needs.