Hi,

I have been attempting to implement a PowerShell script that will access an Excel workbook, check it out, refresh the dataset in the workbook and finally check it back in again.

I've combined this with a task in Windows Task Scheduler to run the script daily from a server with a user account that has access to the SharePoint Online site.

My issue is that the script will not run. When I view the Windows Event logs I can see it is getting a 403 error

The script was taken from the document found here document: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CB0QFjAAahUKEwiBopKGmrXHAhVG6xQKHSIqAPY&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FF%2FA%2F5%2FFA539F71-D658-4C59-A640-2E69CC6FFBA0%2FRefreshDataModelWorkbooksSharePointOnline.docx&ei=E37UVcHMHMbWU6LUgLAP&usg=AFQjCNHF6XR4PwpaHyCPFLOFXbUkaqOyJA&sig2=_SNLhV8mxDYorF5eLD1Gfw&bvm=bv.99804247,d.d24

The the task gets the following script and the location of the Excel Workbook from arguments in the action config of the task (detailed in the document above)

    try
    {
    # Creating the excel COM Object
    $xl = New-Object -ComObject Excel.Application;

    # Setting up Excel to run without UI and without alerts
    $xl.DisplayAlerts = $false;
    $xl.Visible = $false;
    }
    Catch
    {
    Write-EventLog -EventId "5001" -LogName "Application" -Message  "Failed  to start Excel" -Source "Application"
    Exit
    }

    foreach ($i in $args)
    {

    write-host "handling $i"
    try
    {
        # Allow update only if we can perform check out
        If ($xl.workbooks.CanCheckOut($i))
        {

            # Opening the workbook, can be local path or SharePoint URL
            $wb = $xl.workbooks.open($i);

            # Perform the check out
            $xl.workbooks.checkout($i)

            # Calling the refresh
            $wb.RefreshAll();

            # Saving and closing the workbook
            $wb.CheckInWithVersion();

            # in case you are not using checkout/checkin perform a save and close
            #$wb.Save();
            #$wb.Close();

            #Release Workbook
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
        }
        else
        {
            write-host "Check out failed for:  $i"
            Write-EventLog -EventId "5001" -LogName "Application" -Message "Workbook can't be checked out $i" -Source "Application"
        }
    }
    catch
    {
        Write-EventLog -EventId "5001" -LogName "Application" -Message "Failed refreshing the workbook $i $_" -Source "Application"        
    }

    }

    #Quiting Excel
    $xl.quit();

    #Release Excel
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Am I missing something here?

Thanks in advance and please let me know if more info is required.

Hi,

It seems the token expires, please try to click the "Open with Explorer" button again.

As a workaround, we can use .NET client object model to get the file and refresh it, then upload it back to the SharePoint.

Download a File from SharePoint using PowerShell

http://techtrainingnotes.blogspot.de/2014/02/download-file-from-sharepoint-using.html

Office 365 - PowerShell Script to Upload Files to a Document Library using CSOM

http://blogs.technet.com/b/fromthefield/archive/2014/02/19/office365-script-to-upload-files-to-a-document-library-using-csom.aspx

Best Regards,

Dennis Guo

So I got this working finally. Issue was that it wasn't running the script when the option 'User logged on or not' in the general settings of the task was selected. Worked fine when 'User logged on' was selected.

Here are the steps I had to take to get this to run properly.

First the script needed to run from the System32 folder (also specify that directory in the tasks "Start In" box. Also make sure that you are pointing to the 32-bit version of PowerShell since Excel won't work with 64-bit

And second, turns out there is a bug with Excel where you have to create a folder called Desktop in the \SysWOW64\config\systemprofile\ and \System32\config\systemprofile\ directories(both folders need to be created if running Excel 64-bit).

This is the final argument string I ended up using: C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe -nologo noprofile -noninteractive -executionpolicy bypass path\to\script 'path\to\excelworkbook'

• Marked as answer by Peter-Ross Cuthbert 1 hour 39 minutes ago