Using named ranges in other workbooks?

[Blakieto]

I've found that using named ranges very useful. However, I have not found any information for using/accesing the named range in another (unopened) workbook. Is this possible?

I have multiple managers that do their employee schedules in different workbooks. I've managed to get them to all use a standardized format. so I'd like to name the ranges in their workbooks that contain the summary information, so our finance dept can have a single workbook that grabs all this summary into into a different workbook. I'm hoping to accomplish this without typing individual links into each cell... we have staffing data for each week, about a dozen different departments for another dozen different jobs... that is too many links to enter and maintain, (Maintain them as the quarter changes and we drop the old quarter and add the next one a year and a half out...)

I'm open to any suggestions peoples may have for solutions other than named ranges too.

Thanks.
-Blakieto

[lucas]

Blakieto,

I don't know who to give credit to for this. I didn't create it. When I got it you could just import data from cells and I changed it for you to get a range. I hope it works.

you will have 2 books for this example. mine were named main.xls and data.xls

Open a closed workbook in a given directory, retrieve data from it and close it without saving any changes to it.

target is the name of the target sheet for the data!! (in the main.xls excel book that you want to import data into)

Opens data.xls -retrieves data from data.xls-sheet1 range A10-B20 and brings it to range B10-C20 on sheet 1 in the main.xls

the option explicit is very important. and you will have to set up the retrieval ranges and target ranges to suit your purposes. Also the path to the data.xls which you can rename as long as you rename it in the code. I hope this isn't too much info.

put this in a new module

Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Temp\data.xls", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("target")
' read data from the source workbook
        .Range("B10", "C20").Formula = wb.Worksheets("Sheet1").Range("A10", "B20").Formula
End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

I have zipped and attached the two files that I am working on for you to try. If you put them in C:\Temp and run the main.xls you should be able to see it work. Let me know if this doesn't work or isn't what you are looking for because there are some real programmers here who will help you. This place is a great resource.

[Blakieto]

Lucas (Steve?)

thanks! I'll be trying it out this afternoon. I'll post success or less-than-success later today.

-Blakieto

[Blakieto]

I've downloaded and tried the zipped examples, which work fine. However, attempting to use the same logic does not work in my workbooks...

Trying to figure out what is going on, I tried a simple debug routine:

Public Sub WorksheetsReport()
    Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
        MsgBox ThisWorkbook.Worksheets(i).Name & " has index = " & ThisWorkbook.Worksheets(i).Index
    Next i
End Sub

But this causes:

"Run-time error '-2147418113 (8000ffff)':
Automation error
Catastrophic failure"

when control hits the "for i = ..." line. I get the same error in the GetDataFromClosedWorkbook() routine when it reaches the reference to "ThisWorkbook" too.

For reference, here's my version of that routine:

Public Sub GetDataFromClosedWorkbook()
    Dim wb As Workbook
    Dim wks As Worksheet
    Dim path As String
Application.ScreenUpdating = False ' turn off the screen updating
path = "C:\Documents and Settings\senft100\My Documents\blakieto\jobCosting\TD Forecast\"
Set wb = Workbooks.Open(path & "AnimLayTDForecast.xls", True, True)       ' open the source workbook, read only
With ThisWorkbook.Worksheets("AnimLay Schedule")
        ' read data from source workbook
        .Range("Staff").Formula = wb.Worksheets("AnimLay Schedule").Range("Staff").Formula
    End With
wb.Close False      ' close the source workbook without saving any changes
    Set wb = Nothing    ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

which get that same "Catastrophic failure" when control reaches the 'With ThisWorkbook.Worksheets("AnimLay Schedule")' line.

Any ideas? The name of the sheet is "AnimLay Schedule". I've verified that multiple times now... In the Ranges() calls, I'm referencing named ranges; which should be OK, since I have named "Staff" ranges in both workbooks...

-Blakieto

[Blakieto]

Interesting... researching what is causing my problem when I reference "ThisWorkbook". Apparently, because I have all my macros inside an addin, the "ThisWorkbook" variable is returning the addin workbook...

Searching to figure how how I need to modify the logic to handle this...

-Blakieto

[Blakieto]

Indeed, the problems were due to my macros being inside a plugin. Here's the final logic that can grab named ranges inside closed workbooks and copy them into another (open) workbook.

I've also included a copy of the debug macro that did not work, for the cleanest example of referencing a workbook from within a plugin (where "ThisWorkbook" will not work):

Public Sub GetDataFromClosedWorkbook()
    Dim wb As Workbook
    Dim wks As Worksheet
    Dim path As String
Application.ScreenUpdating = False ' turn off the screen updating
path = "C:\Documents and Settings\senft100\My Documents\blakieto\jobCosting\TD Forecast\"
Set wb = Workbooks.Open(path & "AnimLayTDForecast.xls", True, True)       ' open the source workbook, read only
' copy the staff schedules from the opened workbook into the department sheet of the summary workbook:
    wb.Worksheets("AnimLay Schedule").Range("Staff").Copy _
                Application.Workbooks("SummaryTDForecast.xls").Worksheets("AnimLay Schedule").Range("Staff")
wb.Close False        ' close the source workbook without saving any changes
    Set wb = Nothing    ' free memory
Calculate                           ' force a worksheet recalculation
    Application.ScreenUpdating = True   ' turn on the screen updating
End Sub
 
Public Sub WorksheetsReport()
    Dim i As Integer
    Dim wb As String
wb = "SummaryTDForecast.xls"
For i = 1 To Application.Workbooks(wb).Worksheets.Count
        MsgBox Application.Workbooks(wb).Worksheets(i).Name & " has index = " & Application.Workbooks(wb).Worksheets(i).Index
    Next i
End Sub

Note: the 'Calculate' at the end of the routine forces the copied cells to update themselves. Necessary to keep the users from asking why the function does not work! (Even though they could just press F9... but this does that for them.)

Cheers!
-Blakieto

[lucas]

Blakieto,

I tried the files with named ranges and they worked fine. When I try changing the name of the sheet in the data file to a name with a space, I get errors. Might have to use a name with no spaces. I will try to take a closer look when I have a minute. Just quick observations for your info.

[lucas]

Blakieto,
Glad you got it worked out. You didn't have any trouble because of the sheet names?

[Blakieto]

Lucas,

No, having spaces in the sheet names seems to be okay.

Thanks for your help, though. Your suggestion was the main basis of the final working solution.
-Blakieto