none
macro to run ALREADY OPENED personal.xlsb macro on newly opened excel file RRS feed

  • Question

  • i need to create some sort of macro (dos or vbs or a combination) that will run a macro from personal.xlsb (that is ALREADY OPEN) on 4 files opened through a batch file (this could change if needed). 

    1. the 4 files are opened by starting them in a dos batch file (using a saved workspace)

    3. the personal.xlsb macro processes all of the files. 

    2. <--- i just can't figure out how to call the personal.xlsb macro to run when the view opens.  maybe "sendkeys"?  please advise.

    this macro cannot be embedded in the 4 files and cannot run automatically when the files are opened due to distribution.  I distribute dozens of reports daily and need to use this trick a lot of places. 

    i have scheduled access databases to run overnight and i want the excel spreadsheet to populate and publish itself overnight as well.  my existing macros will do this if i can bridge the gap with part 2 above.  otherwise, it all waits until i get to work to push the macro button myself.

    Monday, September 30, 2013 4:01 PM

Answers

  • Application.Run "'Personal.xlsb'!MacroStoredInPersonal"

    Sub MacroStoredInPersonal()

    Msgbox ActiveWorkBook.FullName

    Msgbox ActiveSheet.Name

    End Sub


    Though, of course, you would not want a message box because that will suspend processing...
    Wednesday, October 2, 2013 1:52 PM

All replies

  • Application.Run "'Personal.xlsb'!MacroStoredInPersonal"

    Sub MacroStoredInPersonal()

    Msgbox ActiveWorkBook.FullName

    Msgbox ActiveSheet.Name

    End Sub


    Though, of course, you would not want a message box because that will suspend processing...
    Wednesday, October 2, 2013 1:52 PM
  • OMG I love you!!  Thanks so much!  This looks like it'll work.  I'm going to try it out tomorrow when I get back to work.  This is the magic key!

    You've just enabled me to have my computer do my job for me without my interaction ...well, almost - I still have to babysit it in case the network blips.  But now I can extract data from the system through a series of small databases then open my files to refresh the data and post the reports to the shared drive at work ...all while I'm home asleep in bed.  YAY!!

    I'm going to mark this post solved because I believe in you ...and the code looks perfect for me to adapt.  Thanks a MILLION!!

    • Marked as answer by automatedata Wednesday, October 2, 2013 9:19 PM
    • Unmarked as answer by automatedata Wednesday, October 2, 2013 11:40 PM
    Wednesday, October 2, 2013 9:19 PM
  • I don't believe I've ever received such an enthusiastic response to a simple post - thank you.

    But, you marked your message as the answer, not mine - though that probably won't mislead many people ;-)

    Wednesday, October 2, 2013 11:14 PM
  • Oops.  This is the first time I've requested help.  LOL  Thanks again!
    Wednesday, October 2, 2013 11:41 PM