+ Reply to Thread
Results 1 to 13 of 13

Macro that prompts user to select a file to open and perform another macro on this file

  1. #1
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Macro that prompts user to select a file to open and perform another macro on this file

    Greetings

    I have spent the last couple of days trawling various forums to try and find a solution to my "problem." Basically I have macro (from here onwards will be referred to as Macro 1) which takes a set of data, formats it and then performs a couple of calculations. Macro 1 runs fine and always gives the correct values, however Macro 2 is what has me stumped.

    I want to run Macro 2 in a "summary workbook" so that the user is prompted to select a file which is then opened and then Macro 1 is automatically performed on the selected workbook. Once the values have been calculated these values will then be copied and pasted back into the summary workbook. To summarise:

    1. Run Macro 2 in "Summary workbook"
    2. User is prompted to select another workbook
    3. "other workbook" is then opened and Macro 1 is performed on it.
    4. Once the values in "other workbook" are calculated these are then copied and pasted into the appropriate cells in "Summary workbook". The two cells that will be populated in the "Summary workbook" are next to each other in the same row so I am guessing a simple copy and paste would work but the appropriate cell would have to be selected prior to running macro 2.

    May be asking a lot here, but would like to know if it is at least possible before I expend any further time/energy on this "project"

    Thanks in advance

    Matt
    Last edited by grimmy26; 01-16-2014 at 07:43 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Matt

    Definitely sounds possible.

    Have you tried anything yet?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Well the thing is I don't know how to make my "Macro 1" be executed on the workbook Macro 2 opens. I can get it to prompt the user to open another workbook but it then performs Macro 1 on the summary workbook instead, I assume it has something to do with which is my active workbook?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Not really, it depends on how you are referencing things.

    Can you post a sample of the code in Macro1?

  5. #5
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    I think it will be better to include some code to give you guys an idea of what is going on:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and the code I have used to open another file (the data downloads are actually in csv format):

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Assuming RollTab is Macro1 you could rewrite it like this so that i takes the workbook to run the code on as an argument
    Please Login or Register  to view this content.
    You can then call RollMap for the workbook being opened like this.
    Please Login or Register  to view this content.
    A similar call can be used for the summary workbook, just change wbTarget to ThisWorkbook.

  7. #7
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Ok. Thank you for the quick response! I will have a play around and will let you know how I fare.

    Thanks again!

    Matt

  8. #8
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Hmmmm now time for the "newbie" questions. The "Call RollMap(wbTarget)," is it simply a case of putting this below my sub to open another workbook as it doesn't seem to like it?

    Matt

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    It would look like this.
    Please Login or Register  to view this content.
    By the way, this will only execute the code in RollMap, it won't copy anything to the summary workbook - you didn't post the code for that.
    Last edited by Norie; 01-15-2014 at 02:22 PM.

  10. #10
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    I think it is becoming more and more apparant I don't really understand what I am doing here:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    When I try to run Populate I get the compile error: "ByRef argument type mismatch" with the "wbtarget" part of the code selected and when clicking ok "Sub Populate()" is highlighted in the code.

    Cheers again in advance

    Matt
    Last edited by grimmy26; 01-16-2014 at 03:56 AM.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Matt

    You haven't declared wbTarget in the sub Populate.

    Declare it like this.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    Doh, now that is something I should have known, managed to get the macros to work flawlessly now including the copying/pasting and closing of the spreadsheet called by Populate.

    Thank you once again, whilst I will probably use these forums again, I have purchased an excel/vba book with the hope of learning more and avoid using this place for the more "simple" stuff. Who knows I may even be able to contribute one day, I do find something very satisfying in getting a macro to work.

    Regards

    Matt

  13. #13
    Registered User
    Join Date
    01-15-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro that prompts user to select a file to open and perform another macro on this fil

    And if you are interested the finished code:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Perform macro "on open" specific file- store macro in Personal Macro Workbook?
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2012, 12:38 PM
  2. macro prompts immediately to open a file once the Excel file with the macro is opened
    By jhmayor03 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2012, 10:00 AM
  3. macro that prompts to open another Excel file
    By jhmayor03 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2012, 02:19 PM
  4. [SOLVED] Can I disallow the user to open a file if he disable the macro while the file is opening?
    By Robin in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-07-2011, 04:17 AM
  5. User prompt -> Open file -> Perform action
    By erikhs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2006, 08:55 AM

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