locked
Error while running macro from powershell RRS feed

  • Question

  • Hi ,

    My requirement is to create one excel file and run a macro to fetch some data from web. My micro is well verified manually.

    So I have prepared the below PowerShell script but getting error "Cannot run the macro 'Macro1'. The macro may not be available in this workbook or all macros may be disabled."

    Can anyone help me on this?

    #Create one excel object and set the visibility to false
    
    $excel = New-Object -ComObject excel.application 
    $excel.visible = $false
    
    #Create an excel file
    
    $workbook = $excel.Workbooks.Add()
    
    $outputpath="C:\workbook.xlsx"
    
    
    #Run the predefinded Macro 
    
    $excel.Run('Macro1')
    
    $InfraPortal.SaveAs($outputpath)
    
    $excel.Quit()


    Thanks & Regards, Sasank


    Sunday, July 8, 2018 10:56 AM

Answers

  • Hello sasank Shekhar,

    >>#Run the predefinded Macro 

    How do you predefine the macro?

    A macro is written on a Module which belongs to an specific workbook. As usually, a newly created workbook does not contains any macro. So you would fail to run a macro.

    Usually, I would suggest you predefine the macro via two approaches.

    The one is to create the document based on a macro-enabled template workbook.

    The other is to create a macro add-in so all the workbooks could try to call the macro.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 2:34 AM

All replies

  • Hello sasank Shekhar,

    >>#Run the predefinded Macro 

    How do you predefine the macro?

    A macro is written on a Module which belongs to an specific workbook. As usually, a newly created workbook does not contains any macro. So you would fail to run a macro.

    Usually, I would suggest you predefine the macro via two approaches.

    The one is to create the document based on a macro-enabled template workbook.

    The other is to create a macro add-in so all the workbooks could try to call the macro.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 2:34 AM
  • Thanks Terry for the reply, I have saved the macro in my personal store and now I am able to run the macro and getting the output with the below script

    #Create one excel object and set the visibility to false
    
    $excel = New-Object -ComObject excel.application 
    $excel.visible = $false
    
    #Create an excel file
    
    $InfraPortal = $excel.Workbooks.Add()
    
    $outputpath="C:\Workbook.xlsx"
    
    
    #Run the predefinded Macro 
    
    $macrobook = $excel.workbooks.open("C:\Users\user1\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    
    $excel.Application.Run('PERSONAL.XLSB!Macro1')
    $InfraPortal.SaveAs($outputpath)
    
    $excel.Quit()


    Thanks & Regards, Sasank

    Wednesday, July 18, 2018 9:46 AM
  • Hi Terry,

    Could u plz let me know if there is anyway to integrate the macro code in powershell so that I don't need to load the macro?

    In this scenario I have saved the macro in my personal store so it's working but this won't work in another machine, so if I can integrate the macro code into PowerShell then we can run it even if the macros is not there in personal store.


    Thanks & Regards, Sasank

    Wednesday, July 18, 2018 9:51 AM
  • Hello Sasank Shekhar,

    Glad to hear that your original issue has been resolved. I would suggest mark the helpful reply to close the thread.

    For the issue how to run the macro if it is not  in personal store, please post a new thread for it.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 18, 2018 10:05 AM