none
Powershell to Access to Power BI Desktop

    Question

  • Good afternoon everyone,

    I'm new to scripting in Powershell. 

    If you're still with me, I have Access 365 - 64 bit, and Power BI desktop 64 bit installed on my laptop. I want to set Powershell to run using task scheduler (this I know how to do) but I'm trying to figure out the code for Powershell to launch Access and run the macro. 

    So far; I've run Powershell ISE as an admin, and ran the code to allow scripts to run. 

    Set-ExecutionPolicy RemoteSigned

    Then I found the code which opens Access, and the file; 

    $FileLocation = 'My Access Filepath'
    Start-Process msaccess.exe 'My Access Filepath'

    But what else do I need to make the macro inside the file run? Thank you for any advice! 

    Saturday, April 20, 2019 8:15 PM

All replies

  • You cannot run Microsoft Office programs as a task or without an interactive session.  This is prohibited by Microsoft.

    See: https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office


    \_(ツ)_/

    Saturday, April 20, 2019 8:51 PM
  • Hi JRV,

    Thank you for the response. 

    So then just for my edification, if the user wanted to run the macro would the rest of the script look like this?

    $MsAccess.OpenCurrentDatabase('Databasefile File path')
    $MsAccess.Run('Macro File Path')
    
    Exit

    Sunday, April 21, 2019 6:48 PM
  • No.  You cannot start access then try to  use automation to run it.  You have to create the access object.


    \_(ツ)_/

    Sunday, April 21, 2019 6:58 PM
  • I see now, JRV. I also see that trying to automate using Access in the first place seems kinda redundant as you could just launch the program anyways. Like you could start the process for you go to bed. Anyways, thanks again for the help. 

    Sunday, April 21, 2019 11:06 PM
  • Your question is very confusing.  TO run a macro just load the access com server and access it.

    $accdb = New-Object -ComObject Access.Application

    Now you can run th macro directly.

    You can also specify a "Startup" macro on running an Access db or add a macro call t the command line.

    For help in how to use MSAccess you will need to post in the Office Access forum.  This forum is for administrative scripting.  Also remember that MS clearly states that Office products will not work correctly in a non-interactive session.  That means as a service or under the task scheduler.

    There are other tools that allow Access databases to be queried and updated from a service or task.


    \_(ツ)_/

    Sunday, April 21, 2019 11:22 PM