ExcelBanter - Consolidating multiple files using a row column range

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Consolidating multiple files using a row column range (https://www.excelbanter.com/excel-programming/271349-re-consolidating-multiple-files-using-row-column-range.html)

Consolidating multiple files using a row column range

Thanks Tom,

I did the following:

Selection.Consolidate Sources:=Array( _
"'C:\t\[a.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[b.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[c.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[d.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[e.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[f.xls]Sheet1'!R112C17:R4127C38"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

This did pass syntax, but gave me inaccurate results.

I converted !$Q$112:$AL$4127 to R112C17:R4127C38

Do you think that was right???

TIA,

Jim

-----Original Message-----
Think you need to replace your references with R1C1
notation. This is what
I recorded as an example:

Selection.Consolidate Sources:=Array( _
"'C:\Data\[Alex1.xls]Sheet1'!R3C3:R12C4" _
, "'C:\Data\[Alex2.xls]Sheet1'!R3C3:R12C4"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

Regards,
Tom Ogilvy

"Jim" wrote in message
...
Hi, trying to get the correct syntax for the following
file consolidation.

For files b,c,d,e,f I was using name1

I want to convert them to a specific range of
xls'Sheet1!
$Q$112:$AL$4127"

When I tried for file a, I got a macro error -
consolidation reference is not valid.

Selection.Consolidate Sources:=Array
("'C:\t\a.xls'Sheet1!
$Q$112:$AL$4127", _
"'C:\t\b.xls'!name1", "'C:\t\c.xls'!
name1", "'C:\t\d.xls'!name1", _
"'C:\t\e.xls'!name1", "'C:\t\f.xls'!name1"),
Function:=xlSum, TopRow:=False _
, LeftColumn:=False, CreateLinks:=False

Thanks,

.

Consolidating multiple files using a row column range

set rng = Range("$Q$112:$AL$4127")
? rng.address(true,true,xlr1c1)
R112C17:R4127C38

looks good to me.

Regards,
Tom Ogilvy

"Jim" wrote in message
...
Thanks Tom,

I did the following:

Selection.Consolidate Sources:=Array( _
"'C:\t\[a.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[b.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[c.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[d.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[e.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[f.xls]Sheet1'!R112C17:R4127C38"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

This did pass syntax, but gave me inaccurate results.

I converted !$Q$112:$AL$4127 to R112C17:R4127C38

Do you think that was right???

TIA,

Jim

-----Original Message-----
Think you need to replace your references with R1C1
notation. This is what
I recorded as an example:

Selection.Consolidate Sources:=Array( _
"'C:\Data\[Alex1.xls]Sheet1'!R3C3:R12C4" _
, "'C:\Data\[Alex2.xls]Sheet1'!R3C3:R12C4"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

Regards,
Tom Ogilvy

"Jim" wrote in message
...
Hi, trying to get the correct syntax for the following
file consolidation.

For files b,c,d,e,f I was using name1

I want to convert them to a specific range of
xls'Sheet1!
$Q$112:$AL$4127"

When I tried for file a, I got a macro error -
consolidation reference is not valid.

Selection.Consolidate Sources:=Array
("'C:\t\a.xls'Sheet1!
$Q$112:$AL$4127", _
"'C:\t\b.xls'!name1", "'C:\t\c.xls'!
name1", "'C:\t\d.xls'!name1", _
"'C:\t\e.xls'!name1", "'C:\t\f.xls'!name1"),
Function:=xlSum, TopRow:=False _
, LeftColumn:=False, CreateLinks:=False

Thanks,

.

Consolidating multiple files using a row column range

Thanks Tom,

I gave up on that approach.

I just went through all 60 files and expanded the name1
area manually.

Jim

-----Original Message-----
set rng = Range("$Q$112:$AL$4127")
? rng.address(true,true,xlr1c1)
R112C17:R4127C38

looks good to me.

Regards,
Tom Ogilvy

"Jim" wrote in message
...
Thanks Tom,

I did the following:

Selection.Consolidate Sources:=Array( _
"'C:\t\[a.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[b.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[c.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[d.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[e.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[f.xls]Sheet1'!R112C17:R4127C38"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

This did pass syntax, but gave me inaccurate results.

I converted !$Q$112:$AL$4127 to R112C17:R4127C38

Do you think that was right???

TIA,

Jim

-----Original Message-----
Think you need to replace your references with R1C1
notation. This is what
I recorded as an example:

Selection.Consolidate Sources:=Array( _
"'C:\Data\[Alex1.xls]Sheet1'!R3C3:R12C4" _
, "'C:\Data\[Alex2.xls]Sheet1'!R3C3:R12C4"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False

Regards,
Tom Ogilvy

"Jim" wrote in message
...
Hi, trying to get the correct syntax for the
following
file consolidation.

For files b,c,d,e,f I was using name1

I want to convert them to a specific range of
xls'Sheet1!
$Q$112:$AL$4127"

When I tried for file a, I got a macro error -
consolidation reference is not valid.

Selection.Consolidate Sources:=Array
("'C:\t\a.xls'Sheet1!
$Q$112:$AL$4127", _
"'C:\t\b.xls'!name1", "'C:\t\c.xls'!
name1", "'C:\t\d.xls'!name1", _
"'C:\t\e.xls'!name1", "'C:\t\f.xls'!name1"),
Function:=xlSum, TopRow:=False _
, LeftColumn:=False, CreateLinks:=False

Thanks,

.

.