Merge Multi Workbook Results into One Spreadsheet

dspa0712

New Member
Joined
May 4, 2007
Messages
31
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 :biggrin:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
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

New Member
Joined
May 4, 2007
Messages
31
Put code in and got a syntax error on line:

fn = Dir(myDir & "*.xls"

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

nothing happended
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window (alt f11)

Code:
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

New Member
Joined
May 4, 2007
Messages
31
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

New Member
Joined
May 4, 2007
Messages
31
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

New Member
Joined
May 4, 2007
Messages
31
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.
 

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top