Multi-Wookbook Macro

[JonReyno]

Hi Guys,

Is it possible to have a Macro on a 'Master' Workbook which, when activated, adds the value of the same cell from 4 other Workbooks?

All of the 5 workbooks are exactly the same layout, the 1st one is the Master one which is where I would like the collate inforamtion from the other 4 to go to.

For example: Workbook 1, Cell A1 = 2... Workbook 2, Cell A1 = 1... Workbook 3, Cell A1 = 0 and Workbook 4, Cell A1 = 0. Matser Workbook then takes the information from Cell A1 of each of the workbooks and puts it in to Cell A1 of the Master Workbook to get a total, in this example 3...

I'm clutching at straws and don't know if it's possible, any help would be apprecaited.

Thanks
Jon

 

[jc.021286]

Here we go, give this a shot,
Run through the code with F8 and see if it breaks anywhere
I have made assumptions that there are only 12 monthly tabs and that they are all in order and are named EXACTLY the same as those contained in the master which is EXACTLY the same.

You can see that I have a set of 4 if statements to pull from the loop which file we are accessing. Names of file folders, files and sheets must be EXACTLY the same, or it will break down. Let me know, if there are any issues we will be able to start the debugging process.

Also note, that I Have it set to simply add the numbers to what is already contained, you may wish to set up a stage to clear all data or to specify what months to load / reload.

Regards,
jc

sub Compile_all()
Dim path, MM, SS, FF, arg1, arg2, arg3, arg4, arg5 arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16, arg17 as String

' File location
Path = "S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\" 
    '
' Subfolders
for b = 1 to 4
if b = 1 then MM = "Chris Lawrence" & "\" 
if b = 2 then MM = "Dave Spinner" & "\"
if b = 3 then MM = "Peter Wyatt" & "\"
if b = 4 then MM = "Derek Timms" & "\"
'
'
' File name
if b = 1 then FF = "BSC 2010 - Chris Lawrence" 
if b = 2 then FF = "BSC 2010 - Dave Spinner"
if b = 3 then FF = "BSC 2010 - Peter Wyatt"
if b = 4 then FF = "BSC 2010 - Derek Timms"
'
'
' Sheets
For a = 1 to 12 ' 12 months I hope is being pulled
SS = sheets(a).name
SS = SS & "'!"
'
'
CC1 = Cells(6,7).Address(, , xlR1C1)
CC2 = Cells(7,7).Address(, , xlR1C1)
CC3 = Cells(10,7).Address(, , xlR1C1)
CC4 = Cells(11,7).Address(, , xlR1C1)
CC5 = Cells(12,7).Address(, , xlR1C1)
CC6 = Cells(14,7).Address(, , xlR1C1)
CC7 = Cells(15,7).Address(, , xlR1C1)
CC8 = Cells(16,7).Address(, , xlR1C1)
CC9 = Cells(17,7).Address(, , xlR1C1)
CC10 = Cells(18,7).Address(, , xlR1C1)
CC11 = Cells(20,7).Address(, , xlR1C1)
CC12 = Cells(21,7).Address(, , xlR1C1)
CC13 = Cells(23,7).Address(, , xlR1C1)
CC14 = Cells(24,7).Address(, , xlR1C1)
CC15 = Cells(25,7).Address(, , xlR1C1)
CC16 = Cells(26,7).Address(, , xlR1C1)
CC17 = Cells(27,7).Address(, , xlR1C1)
'
'
' Construct Arguments
arg1 = "'" & Path & MM & "[" & FF & "]" & SS  & CC1
    arg2 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC2
    arg3 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC3
    arg4 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC4
    arg5 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC5
    arg6 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC6
    arg7 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC7
    arg8 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC8
    arg9 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC9
    arg10 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC10
    arg11 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC11
    arg12 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC12
    arg13 = "'" & Path & MM & "[" & FF & "]"  &  SS & CC13
    arg14 = "'" & Path & MM & "[" & FF &  "]"  &  SS & CC14
    arg15 = "'" & Path & MM & "[" & FF &  "]"  &  SS & CC15
    arg16 = "'" & Path & MM & "[" & FF &  "]"  &  SS & CC16
arg17 = "'" & Path & MM & "[" & FF & "]" & SS  & CC17
'
'
' Retrieve all data
GetValue1 = ExecuteExcel4Macro(arg1)
    GetValue2 = ExecuteExcel4Macro(arg2)
    GetValue3 = ExecuteExcel4Macro(arg3)
    GetValue4 = ExecuteExcel4Macro(arg4)
    GetValue5 = ExecuteExcel4Macro(arg5)
    GetValue6 = ExecuteExcel4Macro(arg6)
    GetValue7 = ExecuteExcel4Macro(arg7)
    GetValue8 = ExecuteExcel4Macro(arg8)
    GetValue9 = ExecuteExcel4Macro(arg9)
    GetValue10 = ExecuteExcel4Macro(arg10)
    GetValue11 = ExecuteExcel4Macro(arg11)
    GetValue12 = ExecuteExcel4Macro(arg12)
    GetValue13 = ExecuteExcel4Macro(arg13)
    GetValue14 = ExecuteExcel4Macro(arg14)
    GetValue15 = ExecuteExcel4Macro(arg15)
    GetValue16 = ExecuteExcel4Macro(arg16)
    GetValue17 = ExecuteExcel4Macro(arg17)
'
'
' Input data
sheets(a).Cells(6,7) = sheets(a).Cells(6,7) + GetValue1
sheets(a).Cells(7,7) = sheets(a).Cells(7,7) + GetValue2
sheets(a).Cells(10,7) = sheets(a).Cells(10,7) + GetValue3
sheets(a).Cells(11,7) = sheets(a).Cells(11,7) +GetValue4
sheets(a).Cells(12,7) = sheets(a).Cells(12,7) + GetValue5
sheets(a).Cells(14,7) = sheets(a).Cells(14,7) + GetValue6
sheets(a).Cells(15,7) = sheets(a).Cells(15,7) + GetValue7
sheets(a).Cells(16,7) = sheets(a).Cells(16,7) + GetValue8
sheets(a).Cells(17,7) = sheets(a).Cells(17,7) + GetValue9
sheets(a).Cells(18,7) = sheets(a).Cells(18,7) + GetValue10
sheets(a).Cells(20,7) = sheets(a).Cells(20,7) + GetValue11
sheets(a).Cells(21,7) = sheets(a).Cells(21,7) + GetValue12
sheets(a).Cells(23,7) = sheets(a).Cells(23,7) + GetValue13
sheets(a).Cells(24,7) = sheets(a).Cells(24,7) + GetValue14
sheets(a).Cells(25,7) = sheets(a).Cells(25,7) + GetValue15
sheets(a).Cells(26,7) = sheets(a).Cells(26,7) + GetValue16
sheets(a).Cells(27,7) = sheets(a).Cells(27,7) + GetValue17
next a
next b
End sub

 

[JonReyno]

There is actually an additional tab which is a 'menu' tab. I have put Macro buttons on it for each of the months, will that be a problem?

 

[jc.021286]

depends where it is located and all, though should not be a problem. The macro looks at the names of the sheets in the master file and will attempt to pull the data from the sheets inside the data files with the exact name.

So your only worry is the first 12 tabs on the Master. If the master has one of those menu tabs first, simply change the
For a = 1 to 12
to
For a = 2 to 13

to skip the first tab and still try to pull the correct name.

Let me know,
jc

 

[JonReyno]

OK, I have put the code in and pressed F8 and it has come up with an error which states: 'Compile error: Syntax error'. This is for 'Dim path, MM, SS, FF, arg1, arg2, arg3, arg4, arg5 arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16, arg17 as String'

 

[jc.021286]

I'm terrible with these small mistakes, changes noted below.

OK, I have put the code in and pressed F8 and it has come up with an error which states: 'Compile error: Syntax error'. This is for 'Dim Path, MM, SS, FF, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16, arg17 as String'

Capital P and a missing comma

 

[JonReyno]

OK, that part has worked, had to put a comma in the arg list as well after 5.

I have gone through each step and it comes up with the following error: 'Run-time error '1004': Application-defined or object-defined error'

This came up under 'Retrieve all data' when you get to GetValue2 = ExecuteExcel4Macro(arg2).

Any ideas?

 

[jc.021286]

Incorporate another Dim line at the top with the others
Dim GetValue1,...., GetValue17 as Variant

Try that to see if it will allow it at that point. otherwise I guess it is an issue with the file path construction >.<
if that is the case, it will be a bit more difficult.

If you still have problems after the Dim _ as Variant,
Hover your mouse over the arg1 and note how it has constructed that value after it moves to the next line. If you still have problems, could you please paste that into your next post.

Edit:
simpler would be to add in 2 test lines before that ExecuteExcel4Macro(arg1)
insert 2 lines
cells(100,1) = arg1
Cells(101,1) = arg2

that will insert onto row A100:A101 what the file paths are being constructed as, please include in your post.

Regards,
jc

 

[JonReyno]

I added the 'GetValue1,...17 As Variant' and it's not made any difference. I have also hovered the mouse over the 'arg1' once it's gone past that step, but due to the length of the path name I'm not able to see the complete line as it shows:

'arg1 = "S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score...'

Is there a way I can see the whole line of where it is showing?

If there is a problem using the sub-folders (i.e. Chris Lawrence etc) then I can remove that and just have them all in one folder?

 

[jc.021286]

I thought that would happen,

See my edit in bold in my previous post to make things a bit easier.

Regards,
jc

 

[JonReyno]

OK, the details in those 2 cells are as follows:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="'S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R6C7">S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R6C7</TD></TR></TBODY></TABLE>

&

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="'S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R7C7">S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R7C7</TD></TR></TBODY></TABLE>