Editing External Workbook With Code?

  • Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook).



    Is there maybe a way to specify the external workbook in the Range? i.e.

    Code
    Workbooks("J:\Network Purchasing\CANCELLATIONS\MISC_Practice.xls").Sheet!'Regional Summary'.Range("E2:E50000").Replace What:="AA8983 - Amie Anderson", Replacement:="Anderson, Amie", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False


    ^This is probably wrong, but I have tried multiple combinations of this code, based on examples from the net and can not figure out one that works.


    Have never had any classes in VBA and only fiddled with macros a few times, so I am still learning the basic syntax.


    Can someone please take a look at this:
    MACRO: http://www.geocities.com/sleepeeg3/name_change_macro.xls
    DATA: http://www.geocities.com/sleepeeg3/misc_practice.xls

  • Re: Editing External Workbook With Code?


    Hi


    Try something like


    HTH


    Tony

  • Re: Editing External Workbook With Code?


    Tony, unfortunately it is still trying to run the code in the workbook with the code.


    It gave me a debug error when I used a "." on the sheet name so I used a space.


    ~Garrett

  • Re: Editing External Workbook With Code?


    Quote from sleepeeg3

    Once I open a new workbook, how do I make it the active workbook to have a function make changes to it?


  • Re: Editing External Workbook With Code?


    You need to specify which workbook


    ThisWorkbook refers to the workbook containing the code


    ActiveWorkbook refers to the currently active workbook

  • Re: Editing External Workbook With Code?


    I tried Mrk's code straight up and it does do the change in a new workbook. However, even with his merged into my code, Excel still makes the changes in the default workbook.


    What it must be doing is ignoring the active workbook and taking the "Range" command and applying it to ThisWorkbook. Is there a way I can define the ActiveWorkbook at the same as defining the range?
    Something like "Workbooks("H:\Documents\Troubleshooting Docs\Macros\misc_practice.xls").Worksheets("Region").Range("..."


    It never fails to open the misc_practice.xls. It's just not making changes to it!


    Here is my current code:

  • Re: Editing External Workbook With Code?


    Have you read my code? It is active. MrkFrrl actually showed me how to do that...


    The problem seems to be that I have the code embedded in the sheet, instead of a module. When I unthinkingly put MrkFrrl's code in a module, it worked fine. So I am going to see if calling the module from the toggle button embedded on the sheet will work, as soon as I figure out how to do that.

  • Re: Editing External Workbook With Code?


    That did the trick!


    Here is the final code, fellas:


    Sheet1:


    Module1:


    Module2:


    Thanks for your help, guys. That error code should also come in handy.

  • Re: Editing External Workbook With Code?


    Garrett


    You have code that is running from a commandbutton. This code is in a private sub. By default, it will action items on the original sheet, regardless of whether you have activated another sheet or not.


    To make things work on another workbook / sheet, you have to specify the workbook / sheet you want to action.


    Go back to the original code snip I gave you (which is in keeping with royUK's comments.


    Code
    Set wb = workbooks.open("j:\....") 
    With wb.sheets("region.summary") 
        .Range("E2:E50000").Replace What:="Amie Anderson", Replacement:="Anderson, Amie", LookAt:= _ 
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False


    You set the workbook to use.


    Then you use the with command to use that workbook, and a sheet in the workbook. The dot(.) commands will then work on that workbook.


    I looked at the code in your example, and this structure has to be done for both parts of the operation.


    If you don't understand what I'm are trying to tell you, let me know and I'll see if I can explain in another way. But in your situation, you have to explicitly nominate the workbook / sheet you want to action as you are working with a private sub for a command button.



    HTH


    Tony

  • Re: Editing External Workbook With Code?


    What is this in your code? Where has "w" been defined? Doesn't look right to me as shown.


    Code
    For Each w In Workbooks 
                If w.Name <> ThisWorkbook.Name Then 
                    w.Close savechanges:=True 
                End If 
            Next w


    I guess "w" is a workbook?


    As to directly doing something, I think this might be what you're talking about:


    Code
    Workbooks("WorkBookName.xls").Sheets("SheetName").Range("CellName").Value = "Value to be put in cell"
    ' using a variable for the Window, as previousl mentioned, it would be -
    Workbooks(MyWrkBk).Sheets("SheetName").Range("CellName").Value = "Value to be put in cell"


    I don't believe that the above will work with .Select directly, though you could use the Workbooks().Sheets().Select then Range().Select.
    It might work with .Copy .


    Quote from sleepeeg3

    Is there a way I can define the ActiveWorkbook at the same as defining the range?
    Something like "Workbooks("H:\Documents\Troubleshooting Docs\Macros\misc_practice.xls").Worksheets("Region").Range("..."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!