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,

Thanks for the new screenshots.

With the code you gave me before, I have been able to adjust it to suit accordingly.]/quote]

Please post your updated code.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


'Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]


Have a great day,
Stan

 

[NPike]

Stan,

So far my code is as follows, but I am having difficulties with it.

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 
 
[code]
 
 
Thanks.

 

[stanleydgromjr]

NPike,

You keep changing your requirements, making it more complicated.

See my Private Message to you (top right hand corner of MrExcel, Welcome, NPike., "Private Messages:".


Have a great day,
Stan