locked
Using Powershell to open xlsm, enter password, run macro, save and close.... RRS feed

  • Question

  • Hello

    I'm new to Powershell but keen to automate some repetitive tasks I have to carry out.

    I have 44 spreadsheets, in different shared folders, requiring data to be added to them via a macro in each xlsm.

    Is it possible to open a xlsm, run the macro (i would usually do that by mouse-clicking a button on the spreadsheet) and then save and close it once the data has updated?

    I have tried to work out the script and am stuck with the password section....I can open the xlsm but it prompts me for the password, i want the script to do that...

    $FilePath = "\\server name\folder name\spreadsheet name.xlsm"
    $objExcel = New-Object -ComObject Excel.Application
    $password = "p4ssw0rd"
    $WorkBook = $objExcel.Workbooks.Open($FilePath)

    Can anyone please help me with this script please?

    Thanks

    Tuesday, February 6, 2018 2:42 PM

All replies

  • Hi,

    Based on my research, you can have a try with the following demo script. For your reference, hope it is helpful to you:
    $filePath = '\\server name\folder name\spreadsheet name.xlsm'
    $password = 'p4ssw0rd'
    $macro = 'macro name'
    
    $excel = New-Object -ComObject Excel.Application
    $workBook = $excel.Workbooks.Open($filePath, 0, 0, 5, $password)
    $workSheet = $workBook.Worksheets.Item(1) # Select the first worksheet
    $excel.Run($macro)
    $workBook.Save()
    $workBook.Close()
    $excel.Quit()

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, February 7, 2018 4:42 AM
  • Thanks JRV, there seem to be a lot of different variations to the solution on the internet, i cant decipher which are right/wrong!
    Wednesday, February 7, 2018 12:47 PM
  • The one that works is right.  That is why I posted the documentation for the "Open" method.


    \_(ツ)_/

    Wednesday, February 7, 2018 12:51 PM
  • Thanks Albert, it still asks me for the password manually though :..(

    Wednesday, February 7, 2018 12:54 PM
  • $filePath = '\\server name\folder name\spreadsheet name.xlsm'
    $password = 'p4ssw0rd'
    $xl = New-Object -ComObject Excel.Application
    $wb = $xl.Workbooks.Open($filePath,[type]::Missing,[type]::Missing,[type]::Missing,$password)


    \_(ツ)_/

    Wednesday, February 7, 2018 12:55 PM
  • Hi again,

    SO i must have made a typo because i tried one more time from scratch and it worked...well *kind-of* :/

    The spreadsheet has 2 macros it seems - one called 'OnLoad' and one called 'RunCentral'

    'OnLoad' opens after the password is entered and opens a pop-up box that is a disclaimer requesting user acceptance of the terms by clicking the OK button.

    'RunCentral' is the data import macro.

    I'm now stuck at the 'OnLoad' macro - seems the password is being taken care-of but that pop-up macro seems to require a manual click to accept...Does it require a code change?

    Also, how do we differentiate between macros to be run?

    Thanks for all your help.....it feels like we're really close!!!

    Wednesday, February 7, 2018 1:11 PM
  • You will have to remove the macro.


    \_(ツ)_/

    Wednesday, February 7, 2018 1:13 PM
  • both macros are embedded and cannot be removed...
    Wednesday, February 7, 2018 2:14 PM
  • Hi, both the macros are embedded....so you cant have the 'onload' macro accepted automatically?
    Wednesday, February 7, 2018 4:10 PM
  • Hi,

    I suppose you may need to add an argument for the "onload" macro to make it accept itself automatically. The following link for your reference:
    Application.Run Method (Excel)
    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-run-method-excel

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, February 8, 2018 5:43 AM
  • Hi,

    I suppose you may need to add an argument for the "onload" macro to make it accept itself automatically. The following link for your reference:
    Application.Run Method (Excel)
    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-run-method-excel

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    That cannot work because the open causes the message box to display and the code freezes until it is dismissed.  The "OnLoad" macro runs automatically. 

    There is a way to open a document and not run any macros using the command line.  I  don't see a method with the object model.

    This is supposed to work but it disables all macros.  The macros can still be run from PowerShell but they will not execute  automatically.

    $filePath = 'd:\TEST\book1.xlsm'
    $password = 'p4ssw0rd'
    $xl = New-Object -ComObject Excel.Application
    $xl.AutomationSecurity = [Microsoft.Office.Core.MsoAutomationSecurity]::msoAutomationSecurityForceDisable
    $xl.DisplayAlerts = $false
    $wb = $xl.Workbooks.Open($filePath,[type]::Missing,[type]::Missing,[type]::Missing,$password)
    
    


    \_(ツ)_/

    Thursday, February 8, 2018 5:59 AM
  • Hi,

    I'm checking how the issue is going, was your issue resolved?

    And if the replies as above are helpful, we would appreciate you to mark them as answers, and if you resolve it using your own solution, please share your experience and solution here. It will be greatly helpful to others who have the same question.

    Appreciate for your feedback.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 12, 2018 9:43 AM
  • Hi,

    I am checking how the issue is going, if you still have any questions, please feel free to contact us.

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.

    Appreciate for your feedback.

    Best Regards,
    Albert


    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, February 14, 2018 9:57 AM