Refreshing Excel Sheets using powershell

Question

I have 10 excel different excel sheet in one folder named test. I want to refresh the data connection and data in the pivot table using windows power shell script. The process is using for each loop open each file and then in the file choose powerpivot menu refresh all data there which will refresh the OLEDB and OLAP query and then close that page and go to the data tab and click on the refresh all button and after refreshing work book in each excel sheets there are 5 sheets in 4 out of 5 sheets B4 indicates the month in slicer. we need to choose previous month in all 4 sheets than save the excel.xlsx file and go to the next file do the same process. I have the basic code if someone can help to modify the code. Sheet 1 Sheet 3 Sheet 4 Sheet 5 has the slicer.

#Set the file path (can be a network location)
$filePath = "C:\test"


$excelObj = New-Object -ComObject Excel.Application

$excelObj.Visible = $true

#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)

#Focus on the top row of the "Data" worksheet
$workSheet = $workBook.Sheets.Item("PowerPivot")
$workSheet.Select()
$workSheet.PowerPivotWindow
foreach ($file in $excelfiles)
{
$workSheet = $workBook.Sheets.Item("Home")
$workSheet.Select()
$workBook.RefreshAll()
$excelworkbook.Close()

$workSheet = $workBook.Sheets.Item("Data")
$workSheet.Select()

#Refresh all data in this workbook
$workBook.RefreshAll()


$workBook.Save()
$excelworkbook.Close()
}

#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()

Reynolds Reynolds · Accepted Answer · 2015-09-15T05:33:15

As a suggestion, instead of running that in a script, Adjust your data model to have an attribute you can use. (Like Month Last - Or what you want to call it but it will provide your Last Month Slice) You only need to rely on your $workBook.RefreshAll() to update each sheet. as Last Month or Month Prior will be dependent on your Data and update on the refresh.

You can then Link this Previous Month Slicer to the tables you require to be Previous Month.

Refreshing Excel Sheets using powershell

1 Answers