Merge Multi Workbook Results into One Spreadsheet

[dspa0712]

I have several Excel files (20 at present) all with different filenames saved in a location:

U:\MyWork

Within each workbook is one similar worksheet called "Pro", however there are different amounts of Worksheets called "Ser" on each Workbook.

On each of the Ser sheets are two cell ranges containing values (A1) Name and (B1) Value
eg

1st File
Location: U:\My Work
FileName: 123.xls
Worksheet Names: Pro _Ser1_Ser2_Ser3
Ser1 (A1) value= dfd
Ser1 (B1) value=10
Ser2 (A1) value= dd
Ser2 (B1) value=9
Ser1 (A1) value= de
Ser1 (B1) value=11


2nd File
Location: U:\My Work
FileName: 333.xls
Worksheet Names: Pro _Ser1
Ser1 (A1) value= db
Ser1 (B1) value=1


What I want is a master spreadsheet to 'suck up' data from all the Ser sheets within all Workbooks and populate the master spreadsheet with Column A =A1 Values from all the Workbook, Worksheets
Column B=B1 Values from all the Workbook, Worksheets

Output on Master Workbook will look like

Column A_____________________Column B

dfd___________________________10
dd____________________________9
de____________________________11
db____________________________1


Help please

 

[jindon]

try

Sub test()
Dim myDir As String, fn As String, ws As Worksheet, LastR As Range
myDir = "U\My Work"
If Dir(myDir, vbDirectory) = "" Then
     MsgBox "Wrong directory"
     Exit Sub
End If
fn = Dir(myDir & "\*.xls")
If fn = "" Then
     MsgBox "No .xls file in " & myDir
     Exit Sub
End If
Do While fn <> ""
     With Workbooks.Open(myDir & fn)
          For Each ws In .Worksheets
               If ws.Name Like "Ser*" Then
                    Set LastR = ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
                    LastR.Resize(,2).Value = Array(ws.Range("a1").Value, ws.Range("b1").Value)
              End If
          Next
          .Close False
     End With
     fn = Dir()
Loop
End Sub

Edited:Code

 

[dspa0712]

Put code in and got a syntax error on line:

fn = Dir(myDir & "*.xls"

I put a ")" after .xls" and ran

nothing happended

 

[jindon]

OOps, sorry about ")"

If you run and nothing happen, 2 possibilities.

Either your directory is wrong or you have no .xls file in that folder.

I will fix previous code, so try again.

Code has been edited and I have to go off-line now, so if anything, I will reply tomorrow.

 

[ravishankar]

Hi
Paste the following codes in the macro window (alt f11)

Sub pull()
Cells(2, 1).Select
F = Dir("U:\MyWork\" & "*.xls")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select 'By row
F = Dir()
n = n + 1
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
b = Cells(a, 1)
Cells(a, 3) = "='U:\MyWork\[" & b & "]ser1'!A1"
Cells(a, 4) = "='U:\MyWork \[" & b & "]ser1'!B1"
Cells(a, 5) = "='U:\MyWork \[" & b & "]ser2'!A1"
Cells(a, 6) = "='U:\MyWork \[" & b & "]ser2'!b1"
Cells(a, 7) = "='U:\MyWork \[" & b & "]ser3'!A1"
Cells(a, 8) = "='U:\MyWork \[" & b & "]ser3'!B1"
Next a
End Sub

Run the macro. If it works OK, you can alter sheet names to suit your case.
Ravi

 

[dspa0712]

Thanks ravishankar

Copied your code into the macro window and only thing was that on the Master sheet where I am executing the code from:

The cursor moved from A1 to A2 but no data populated ????

 

[ravishankar]

Hi
I have tested the macro and found it OK before it was posted. The only difference is, I have copied your file path (u:\my work) instead of mine. Check if it is copied correctly and you have excel files in that folder
ravi

 

[dspa0712]

I get this:


123.xls dfd #REF! #REF! #REF! #REF! #REF!
333.xls db #REF! #REF! #REF! #REF! #REF!


When executing your code, also a whole series of "File Not Found" errors

On this master sheet is it not possible for the code to only populate Column A being the name of the workbook
Column B being A1 values for all the Ser sheets in that Workbook
Column C being B1 values for all the Ser sheets in that Workbook

Not sure what the #Ref error are?????

 

[ravishankar]

Hi
The code is intended to list files in col A , and value of ser1, ser2, ser3 in CD,EF, GH. #ref shows folder / file /sheet is missing.
check the cell with error to find what formula it has. It should follow the pattern
='path[filename.xls]sheetname'!A1
since your are getting dfd and db, those formula appear OK. probably the sheets may not exist in the files you are looking into.
Ravi

 

[dspa0712]

I have checked the code and the path is correct:

On the master sheet after I have ran the code

A2=123.xls with no formula in forumla bar
A3=333.xls with no formula in formula bar
C2=dfd formula bar =[123.xls]Ser1!A1
D2=#REF formula bar ='U:\MyWork\[123.xls]Ser1'!B1
E2=#REF formula bar ='U:\MyWork\[123.xls]Ser2'!A1
F2=#REF formula bar ='U:\MyWork\[123.xls]Ser2'!B1
G2=#REF formula bar ='U:\MyWork\[123.xls]Ser3'!A1
H2=#REF formula bar ='U:\MyWork\[123.xls]Ser3'!B1

Same results appear across the page for 333.xls


When I manually change cell D2 formula to =[123.xls]Ser1!B1

it draws the correct info

Im puzzled now can you fix


Sorry for an unclear explanation but I only want three columns to hold data

Column A being the file name eg 123.xls
Column B being the service name eg dfd
Column C being a numberic value eg 10

If on Ser2 or Ser3 or Ser4 a different service name appears then this will be listed on the next free row

So the intended output would be:

Column A may contain multiple similar entries
Column C will hold unique values
Column D will hold possible duplicate values

list down the page

Other thing is that your code appears to have a limit on the workbooks having only 3 Ser worksheets within them.

Can you amend the code so that it runs x amount of times depending on how many Serv are within the Workbook
Is this clear or do you need futher explanation.