Refreshing an Excel file connected to SAP BW

[mmostaquim]

Hi,

I have a project in which there is an Excel file in SharePoint. This Excel file is connected to SAP BW. It needs to be refreshed every day.

I have already read some of the articles published in the BP Community about SharePoint and refreshing an Excel file. For SharePoint it is suggested to use SharePoint Mapping, Microsoft Graph API, HTTP request etc. and for Excel refresh it is suggested to use Autocalculate action from Excel VBO.

I prefer to use SharePoint Mapping to open the Excel file with File Management VBO and refresh the Excel file connected to SAP BW without downloading the file from SharePoint. My question is whether the autocalculate action of the Excel VBO works for the purpose of SAP BW refresh? And does the refresh work without downloading the file to the local drive?

Moreover, I am open to any other ideas from you.

[SayeedBinAbdullah]

Hi @mmostaquim 

If the Excel file is opened via SharePoint Mapping, it behaves much like a locally opened file, allowing you to interact with it. However, whether the refresh function works in this scenario depends on how Excel interacts with the external data connection when opened this way. Some refresh operations require local execution rather than running directly on SharePoint.

Alternatively, you could write a macro that triggers the refresh action when the file is opened.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
ThisWorkbook.RefreshAll ' Refresh all data connections
Application.ScreenUpdating = True
End Sub

• This code runs whenever the Excel file is opened.

• It refreshes all data connections automatically.

•  Application.ScreenUpdating = False temporarily hides updates to improve performance.

Also if feasible, PowerShell scripts can automate Excel refresh and data retrieval from SAP BW without direct manual intervention.

Steps to Refresh Excel via PowerShell:

1. Ensure Excel is Installed: Since PowerShell interacts with the Excel COM object, Excel must be installed on the machine running the script.

2. Map SharePoint as a Network Drive: If using SharePoint Mapping, map your SharePoint site as a network drive (e.g., Z:\ drive). You can use:

   net use Z: h t t p s : //yoursharepointsite/Documents /persistent:yes

   This allows direct access to the Excel file in SharePoint.

3. Open Excel via PowerShell & Refresh Data: You can use PowerShell to open the Excel file, trigger a data refresh, and save the file:

   $Excel = New-Object -ComObject Excel.Application
   $Excel.Visible = $false # Run Excel in background

   $Workbook = $Excel.Workbooks.Open("Z:\YourExcelFile.xlsx")
   $Workbook.RefreshAll() # Refresh SAP BW data connections

   Start-Sleep -Seconds 10 # Wait for refresh to complete
   $Workbook.Save()
   $Excel.Quit()

4. Automate Execution via Task Scheduler:

   • Save the PowerShell script as .ps1

   • Schedule the script to run daily using Windows Task Scheduler.

Best Regards,

Sayeed Bin Abdullah