Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating multiple worksheets into one | Excel Worksheet Functions | |||
Consolidating multiple worksheets into one. | Excel Discussion (Misc queries) | |||
need help in consolidating multiple data | Excel Discussion (Misc queries) | |||
Consolidating multiple worksheets | Excel Discussion (Misc queries) | |||
Consolidating multiple workbooks | Excel Worksheet Functions |