- How to create a script that opens an Excel file, refreshes all external data connections, saves the Excel file and closes
Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
In Microsoft Excel 2010 (Windows) you can connect an Excel file directly to a few different data sources:
- Microsoft Access
- Web tables
- Text file
- SQL Server
- Analysis Services
For a proof of concept, we actually created an Excel file that was extracting data directly from an Analysis Services cube that was hosted in the cloud. It was great that we could get the data into the spreadsheet but we needed to figure out how to get new data and push it to Roambi Business automatically.
We created a VBScript file that essentially opens a specified Excel file, refreshes the data connections, saves the Excel file, closes the Excel file and quits Excel. We took this VBScript and embedded it into a cmd file that invoked RoambiScript to upload this same excel into a Roambi Business Library once the VBScript has completed.
This article will walk you through the two files that achieve the data refresh and push to cloud:
- Microsoft Excel
- Excel spreadsheet connected to an external source via Excel's built-in data connectivity
- RoambiScript (all necessary pieces, etc)
The first step is to create the .vbs file. Here is a screenshot of the vbs file (also attached) with some annotations explaining the key items.
1. Launch Excel
2. Open msdb2012.xlsx
3. Refresh all External Data Connections
4. Save the Excel workbook
5. Close the Excel workbook
6. Quit Excel
The next step is to call this script from a cmd file. Here's a screenshot of the cmd file (also attached) with some annotations explaining the key items:
1. Close any instance of Excel
2. Invoke the VBScript
3. Run RoambiScript to push the latest instance of the Excel file to the Roambi Library
KBA , BI-ROM-CLD-SRC , Roambi Cloud Service , How To