Consulting

Results 1 to 9 of 9

Thread: Solved: using named ranges in other workbooks?

  1. #1
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location

    Solved: using named ranges in other workbooks?

    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

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    This Might work for your purpose

    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

    [VBA]
    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

    [/VBA]

    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location
    Lucas (Steve?)

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

    -Blakieto

  4. #4
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location

    seem to be having troubles...

    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:

    [vba]
    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
    [/vba]

    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:

    [vba]
    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
    [/vba]

    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

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location

    little more info...

    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

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location

    Smile Solved!

    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):

    [vba]
    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
    [/vba]

    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

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Blakieto,
    Glad you got it worked out. You didn't have any trouble because of the sheet names?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    VBAX Regular
    Joined
    Jun 2004
    Location
    Los Angeles
    Posts
    22
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •