Question on accessing multiple workbooks to form a master database

[NPike]

Please Help!

I have 100 or so workbooks named A1234.xls, A1235.xls, A1236.xls etc.

I want to gather information from the same worksheet and same cell in each workbook.

I want this information in a separate workbook named Master.xls

In the Master spreadsheet I have the workbook names in column A e.g.
A1234
A1235
A1236 etc.

In column B, I want the information form each of the 100 workbooks. For example, from "sheet 1" Cell C2. This is the same place I want the information from in each workbook.

Is there an easy way of doing this with a formulae rather than a macro. For example, in the Master spreadsheet column B1 formula would read =[A1234.xls]Sheet1!$C$2 ... and then can you drag this changing the filename according to column A in the master spreadsheet?

If not can a macro be used? I have only started looking at macro's and my knowledge on them is very basic.

Any help would be very much appreciated.

Thanks in advance!

 

[stanleydgromjr]

NPike,

Here you go.

Tested with four workbooks, and in each cell C2, is the four digit number of the file name (as an example).


Before the macro:

Excel Workbook
	A	B
1	Workbook Name	C2 Value
2	A1234
3	A1235
4	A1236
5	A1237
6	A1238
Sheet1

After the macro:

Excel Workbook
	A	B
1	Workbook Name	C2 Value
2	A1234	1234
3	A1235	1235
4	A1236	1236
5	A1237	1237
6	A1238	1238
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub GetData()
    Dim MyDir, FileName, SheetName As String
    Dim LR, Ctr As Long
    Application.ScreenUpdating = False
    
    '**************************************************
    'Change MyDir to your directory/folder path
    'MyDir = "C:\TestData"
    '**************************************************
    
    MyDir = "C:\TestData"


    '**************************************************
    'Change SheetName to your sheetname
    'SheetName = "Sheet1"
    '**************************************************
    
    SheetName = "Sheet1"


    With ActiveSheet
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        For Ctr = 2 To LR Step 1
            FileName = Cells(Ctr, 1).Value & ".xls"
            With .Cells(Ctr, 2)
                .Formula = "='" & MyDir & "\[" & FileName & "]" & SheetName & "'!C2"
                .Value = .Value
            End With
        Next Ctr
    End With
    Application.ScreenUpdating = True
End Sub

Make sure you change the "MyDir = "C:\TestData" in the above code to match your directory/folder path.


Then run the "GetData" macro.


Have a great day,
Stan

 

[NPike]

Thanks Stan!

Works really well. More complicated than I first thought!

If I was wanting to change the cell I was copying from each workbook from C2 to say another cell, for example AL203, what part of the Macro do I have to alter and how?

Also if I was wanting to change the destination in the Master workbook from B2 to say D8, what part of the Macro do I have to alter?

Thanks once again.

 

[stanleydgromjr]

NPike,

The answer to your questions are in the below code.

Option Explicit
Sub GetData()
    Dim MyDir, FileName, SheetName As String
    Dim LR, Ctr As Long
    Application.ScreenUpdating = False
    
    '**************************************************
    'Change MyDir to your directory/folder path
    'MyDir = "C:\TestData"
    '**************************************************
    
    MyDir = "C:\TestData"


    '**************************************************
    'Change SheetName to your sheetname
    'SheetName = "Sheet1"
    '**************************************************
    
    SheetName = "Sheet1"


    With ActiveSheet
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        
        'If your File Names in column A begin in row 8
        'change the next line of code
        'For Ctr = 2 To LR Step 1
        'to
        For Ctr = 8 To LR Step 1
            FileName = Cells(Ctr, 1).Value & ".xls"
            
            'If you want to change the destination cell from B2 To D8
            'change the next line of code
            'With .Cells(Ctr, 2)
            'to
            With .Cells(Ctr, 4)
            
                'copying from each workbook from C2 to say another cell, for example AL203
                'change the next line of code
                '.Formula = "='" & MyDir & "\[" & FileName & "]" & SheetName & "'!C2"
                'to
                .Formula = "='" & MyDir & "\[" & FileName & "]" & SheetName & "'!AL203"
                
                .Value = .Value
            End With
        Next Ctr
    End With
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[NPike]

Stan,

Thanks for the help. I have tried to alter the macro slightly as aforementioned. The problem I am getting now is everytime I run the Macro an "Update Values" box pops up and I have to choose the desitnation folder and file. This did not happen in your original example and if possible I would not like it to happen on my altered spreadsheet. Can you spread any light on this for me? I have copied the code I am using below.

For reference:

In the spreadsheet I am running the Macro, the scheme names start in E11, and the results, I want to start in F11. The spreadsheets I am getting the information from are all cell D31. Hope this gives you enough detail.

Thank you very much.

Neil.



Option Explicit
Sub GetData()
Dim MyDir, FileName, SheetName As String
Dim LR, Ctr As Long
Application.ScreenUpdating = False

'**************************************************
'Change MyDir to your directory/folder path
'MyDir = "C:\TestData"
'**************************************************

MyDir = "K:\Lee\CM\YWS\Area Framework Cost Validation\Test Schemes"

'**************************************************
'Change SheetName to your sheetname
'SheetName = "Sheet1"
'**************************************************

SheetName = "Scheme Overview"

With ActiveSheet
LR = .Cells(Rows.Count, 5).End(xlUp).Row
For Ctr = 11 To LR Step 1
FileName = Cells(Ctr, 1).Value & ".xls"
With .Cells(Ctr, 6)
.Formula = "='" & MyDir & "\[" & FileName & "]" & SheetName & "'!D31"
.Value = .Value
End With
Next Ctr
End With
Application.ScreenUpdating = True
End Sub

 

[NPike]

Stan I was wondering if you could just give me one last help? With the code you gave me before, I have been able to adjust it to suit accordingly. There is just one last thing which I am struggling with.

In the worksheet with the Macro in, column A has the workbook scheme names in, and column B has the returned values I am looking for in each workbook.

How do I change the Macro so that the scheme names (e.g. A1234) are in say column F?

Thanks once again for your help.

Neil.

 

[stuarthammond]

If you don't want to use a macro, why not try Data > Consolidate? I use this to consolidate one row of data from each workbook, up to around 100 or more in some case. It's easy to set up - in the source workbooks, create a named range including header row (the header row should be the same in each file), then just use the above function to consolidate the same range name for each different file.

Regards,
Stuart

 

[NPike]

Stuart,

I would much prefer not to use a Macro. Can you expand on this method you described. Note. The main problem I have is there is around 2000 workbooks and I can't drag the formulae in the Summary spreadsheet to change file references.

Cheers, Neil.

 

[stanleydgromjr]

NPike,

Stan I was wondering if you could just give me one last help? With the code you gave me before, I have been able to adjust it to suit accordingly. There is just one last thing which I am struggling with.

In the worksheet with the Macro in, column A has the workbook scheme names in, and column B has the returned values I am looking for in each workbook.

How do I change the Macro so that the scheme names (e.g. A1234) are in say column F?

Screenshots would really help.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[NPike]

Hi Stan,

Hope this helps.

I actually have a few more functions with the macro's I need to use.

1) The first one is to copy the cells below (which are on all the the 1000's of spreadsheets named A1234 etc) - AL23 to AQ23

Excel Workbook
	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
17
18
19
20
21
22			Gateway 1	Gateway 2	Gateway 3	Gateway 4	Gateway 5	Gateway 6
23			19-Jan-09	19-Jan-09	19-Jan-09	19-Jan-09	19-Jan-09	19-Jan-09
24
25
Scheme Overview

Copy onto the spreadsheet below (the Master Summary workbook) - B11 to G485

Excel Workbook
	A	B	C	D	E	F	G	H
3	GATEWAY PROCESS MAP
4
5								%
6		22%	0%	11%	0%	0%	0%	Nr.
7	Scheme Nr.	2	0	1	0	0	0	Scheme
8	9
9
10	Schemes	Originally Received	Received Information all Complete	Commencement of Level Checks	Issues Identified	Issues Resolved	Recommendations made
11	A1234	28-Jan-09		01-Jan-09				1
12	A1235	30-Jan-09						2
13	AMP4-in-4 A1236 613	30-Jan-09						3
14	etc							4
Gateways

2) Also need to copy these cells from the 1000's of workbooks, from AL37 to AS37

Excel Workbook
	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
34
35		General Costs	All "People" Costs	Equipment Costs	Material Costs	AOH Costs	Asset Lives	Opex	General
36
37		0	0	0	1000000	0	0	0	1E+06
38
39
Scheme Overview

To the Master summary workbook - I486 to P486.

Excel Workbook
	H	I	J	K	L	M	N	O	P
483	A1232	1000000		1000000			1000000	1000000
484	A1233	1000000
485	A1234	0	0	0	1000000	0	0	0	1000000
486
487	Scheme	General Costs	All "People" Costs	Equipment Costs	Material Costs	AOH Costs	Asset Lives	Opex	General
Data

3) And lastly, copy from the 1000's of workbooks AJ102 to IU102

Excel Workbook
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
97
98
99
100		R
101		L1Q1	L1Q2	L1Q3	L1Q4	L1Q5	L1Q6	L1Q7	L1Q8	L1Q9	L1Q10	L1Q11	L1Q12	L1Q13	L1Q14	L1Q15	L1Q16	L1Q17	L1Q18	L1Q19	L1Q20	L1Q21	L1Q22
102		0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
Scheme Overview

Copied to, E10 to HP10 in the Master Summary workbook.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O
6
7		Reds
8	Totals	0	1	0	0	0	0	0	0	0	1	0
9	Scheme	L1Q1	L1Q2	L1Q3	L1Q4	L1Q5	L1Q6	L1Q7	L1Q8	L1Q9	L1Q10	L1Q11
10	A1234	0	1	0	0	0	0	0	0	0	1	0
11	A1235	0
12	AMP4-in-4 A1236 613	0
13	etc	0
14	0	0
15	0	0
Areas of Concern

I am so greatful for your help, you are saving my life!

Cheers Stan,

Neil.