PDA

View Full Version : Named Range reference to remote workbook



tnewman
10-12-2010, 05:05 AM
I've been at this problem for a few days and still haven't been able to overcome this particular issue.

I designed my add-in to be able to export all named ranges in an active workbook, manipulate, and restore the ranges. My problem occurs when I have local named ranges referring to other workbooks. I can add the ranges only with the other workbook open. I'm trying to get this solved for a specific business application, and could use some help.

I've tried quite a few iterations of the following:

...
ActiveWorkbook.Names.Add Name:=.Cells(2 + I, 1).Value, _
RefersTo:=Workbooks(strPath & "[" & .Cells(2 + I, 3).Value & "]").Worksheets(.Cells(2 + I, 4).Value).Range(.Cells(2 + I, 5).Value)
...
where the cell references refer to the workbook name ("A Name.xlsx"), the worksheet name, and the "$A$1:$B$2" reference.

Any help would be appreciated.

Jan Karel Pieterse
10-12-2010, 06:18 AM
The refersto property is a string, not a range object, so:

Refersto:= "='" & strPath & "[" & .Cells(2 + I, 3).Value & "]" & .Cells(2 + I, 4).Value & "'! & .Cells(2 + I, 5).Value

Note the single quotes...

You may have to set displayalerts to false to avoid Excel prompting for the file if is isn't open.

tnewman
10-12-2010, 09:38 AM
Works like a charm. My confusion is that it works as a range object for local ranges and for ranges in other workbooks if that workbook is open.

Thanks Again.