Data Consolidation using dynamic path

[Jholerjo]

Hi All,

I need help with data consolidation. I have hardcoded file for data
consolidations as follows:

Range("B6").Select
Selection.Consolidate Sources:=Array( _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk1.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk2.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk3.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk4.xls]Summary'!R6C2:R17C7" _
), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
False
Range("B19").Select

It works. BUt when I change that to variables using this code:

Sub Consolidate_Cured()
'
Dim strFilePath As String
Range("B6").Select
strFilePath = ActiveWorkbook.Path

Selection.Consolidate Sources:=Array( _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk1.xls]Summary'!R6C2:R17C7" _
, _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk2.xls]Summary'!R6C2:R17C7" _
, _
" ' "& strfilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk3.xls]Summary'!R6C2:R17C7" _
' _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk4.xls]Summary'!R6C2:R17C7" _
), Function:=xlSum, TopRow:=False, LeftColumn:=false, CreateLinks:= _
False

Range("B19").Select
End Sub

and VB return error said :compile error expected: List separator or ).
Can Anybody help me with the syntax please?

Thank You very3x much...
Jeff

 

[Tom Ogilvy]

Maybe something like this - what word wrap in the email:

Selection.Consolidate Sources:=Array( _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk1.xls]Summary'!R6C2:R17C7", _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk2.xls]Summary'!R6C2:R17C7", _
" ' " & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk3.xls]Summary'!R6C2:R17C7", _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk4.xls]Summary'!R6C2:R17C7"), _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=False, _
CreateLinks:=False