Consolidate - Relative Reference

[Skip]

Hi all,

A while ago I asked about making a Macro portable (i.e. relative
references) - someone kindly responded but at the time it was beyond me to
implement. I have learned since then and have now managed to implement -
basically, open, manipulate, close and save workbooks is working fine.
However, in my 'summary' workbook I consolidate data from 4 other workbooks
and I'm struggling to edit the code to make this portable. My current code
follows and although it doesn't produce any errors, it does not consolidate
the data (if I quote the full path it works fine). The summary workbook is
always in the parent directory above Q1, Q2, Q3 and Q4:

Range("A7").Select
Selection.Consolidate Sources:=Array( _
ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q2\[SummaryQ2.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q3\[SummaryQ3.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q4\[SummaryQ4.xls]01'!R7C1:R205C20"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Am I making a basic mistake or is it not quite as simple as I'm trying to
make it?

Many thanks.

Paul.

 

[Tom Ogilvy]

You have your first single quote in the wrong place

ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

should be

"'" & ActiveWorkbook.Path & "\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

otherwise it appears in the middle of your path.

 

[Skip]

Many thanks Tom,

Now works as I'd hoped.

Paul.

You have your first single quote in the wrong place

ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

should be

"'" & ActiveWorkbook.Path & "\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

otherwise it appears in the middle of your path.

--
Regards,
Tom Ogilvy

Hi all,

A while ago I asked about making a Macro portable (i.e. relative
references) - someone kindly responded but at the time it was beyond me to
implement. I have learned since then and have now managed to implement -
basically, open, manipulate, close and save workbooks is working fine.
However, in my 'summary' workbook I consolidate data from 4 other workbooks
and I'm struggling to edit the code to make this portable. My current code
follows and although it doesn't produce any errors, it does not consolidate
the data (if I quote the full path it works fine). The summary workbook is
always in the parent directory above Q1, Q2, Q3 and Q4:

Range("A7").Select
Selection.Consolidate Sources:=Array( _
ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q2\[SummaryQ2.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q3\[SummaryQ3.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q4\[SummaryQ4.xls]01'!R7C1:R205C20"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Am I making a basic mistake or is it not quite as simple as I'm trying to
make it?

Many thanks.

Paul.