+ Reply to Thread
Results 1 to 17 of 17

Code to open workbook and change value in a specific cell

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Code to open workbook and change value in a specific cell

    This code is embedded in the workbook "Copy of Combine Test"

    I am trying to have this code open up every workbook in the specified folder and then copy the range "A6:A7" from the "Copy of Combine Test" workbook and paste it into the range "B5:B6" in every workbook that is opened. Can anyone help?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    I think something like this would work better. I couldn't test because I don't have your directory and files, but if it doesn't work let me know which line give an error.

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    This part of the code is giving me the error object does not support this property or method

    Please Login or Register  to view this content.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    Unfortunately, I often get mixed up with all the requirements for the Paste method vs PasteSpecial. This should work, but some of it may be unnecessary:

    Please Login or Register  to view this content.
    If this is overkill a guru please correct me with what is needed, thanks.
    Last edited by davegugg; 10-29-2010 at 03:55 PM. Reason: Put the sheet that was active when the workbook was opened back to the active sheet.

  5. #5
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    now I am getting the same error for Activecell.paste

  6. #6
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    Let's try to simplify it:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    I got it..I had to change the line to ActiveCell.PasteSpecial (xlPasteValues)

    After the data copies to ActiveSheet.Cells(5, 2) I would like to run a macro on the workbook that has just opened call "RunResults". Do you know how this can be done?

  8. #8
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Code to open workbook and change value in a specific cell

    Hi everyone,

    We can shorten Dave's code slightly again (I think!?) to read as below, with the aim being to minimise the repeated actions within the loop (ie activating the "master" workbook each time).

    Please Login or Register  to view this content.
    If you only want the values to be transferred & you aren't concerned about formatting etc, you can probably (untested!) use...

    Please Login or Register  to view this content.
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  9. #9
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    If the macro is in the workbook that you are currently running code from, you just have to put RunResults on a line while the workbook you want to run it in is active. If the macro is in the workbook that you have opened, you have to do something like Application.Run "MacroBook!MacroName" .
    Just Google excel vba run macro another workbook for details.

  10. #10
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    Thanks broro for youe help. I used the first range conversion and it runs perfectly.

    The macro RunResults is in the workbook that I have opened. What would I insert as the workbook name in the application.run line? Every workbook has a different name but has the RunResults macro included in it.

    Application.Run "MacroBook!RunResults"

  11. #11
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    It should be:

    Please Login or Register  to view this content.

    Thanks for the help Rob, I just couldn't quite nail it!

  12. #12
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    I am not exactly sure why but i am getting an error that the macro may not be available in this workbook. The macro is definitly there, I don't know why it is not recognized

  13. #13
    Registered User
    Join Date
    10-21-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    2

    Re: Code to open workbook and change value in a specific cell

    I'm going to suggest looking at adding a reference to the Microsoft scripting library (you'd find that under "Tools >> References >>" then check the box next to "Microsoft Scripting Runtime") and working from there and using objects and methods such as "for each file" .. etc. since it sounds like you don't know how many files are going to be in there, or what they might be called.

    I'm currently beating my head against a project of my own, so I can't go into much more detail and I'm not sure I understand exactly what you're asking, but this could get you closer ....

    Also, you could both define your range as an object, and your source workbooks as objects ..... that will speed up your code, I believe?

    Hope this helps you down a path of tighter, better, faster executing code .... I am by no means an expert, but have scripted out similar things recently for my own use.

    I'm sorry that this doesn't correlate to your question above, maybe it's not scoped properly?

  14. #14
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to open workbook and change value in a specific cell

    Try putting your actual workbook name in single quotes like this:

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Code to open workbook and change value in a specific cell

    Thanks the macro RunResults takes about 30 seconds to run. Is there a way to pause the code to allow it to run

  16. #16
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Code to open workbook and change value in a specific cell

    hi all,

    Dave/Rhudgins
    You were so close, all I've offered is a couple of small changes

    Rhudgins,
    What does the "RunResults" macro do?
    To use someone else's words...
    Application.Run is synchronous. The call to Application.Run("Macro_1") will not return until the macro has finished executing. That said, if the macro starts an asynchronous process, then you might get the effect you describe. What do the macros do, and what evidence do you have that the macros are running in parallel? – Gary McGill Nov 9 '09 at 20:44
    Quote Originally Posted by toryb View Post
    ...you could both define your range as an object, and your source workbooks as objects ..... that will speed up your code, I believe?
    ...
    Toryb,
    If the range & the source workbooks were being referred to repeatedly, then, yes, I'd agree that defining them explicitly* would be likely to speed up the code. However, in this example, they are only referred to once.
    *Also, rather than defining them as "objects", I'd suggest defining as the variable types that they are (ie "as Range" & "as Workbook").

    Rob

  17. #17
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Code to open workbook and change value in a specific cell

    Deleted duplicate post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1