使用 powershell 刷新 Excel 表格 [英] Refreshing Excel Sheets using powershell

问题描述

我在一个名为 test 的文件夹中有 10 个不同的 excel 工作表.我想使用 windows power shell 脚本刷新数据透视表中的数据连接和数据.该过程使用每个循环打开每个文件，然后在文件中选择 powerpivot 菜单刷新所有数据，这将刷新 OLEDB 和 OLAP 查询，然后关闭该页面并转到数据选项卡，然后单击刷新所有按钮刷新每个excel表中的工作簿，5张纸中的4张中有5张B4表示切片器中的月份.我们需要在所有 4 个工作表中选择上个月而不是保存 excel.xlsx 文件并转到下一个文件执行相同的过程.如果有人可以帮助修改代码，我有基本代码.Sheet 1 Sheet 3 Sheet 4 Sheet 5 有切片器.

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()

推荐答案

建议不要在脚本中运行它，而是调整您的数据模型以具有您可以使用的属性.(比如上个月 - 或者你想叫它什么，但它会提供你上个月的切片)你只需要依靠你的 $workBook.RefreshAll() 来更新每个工作表.因为上个月或前一个月将取决于您的数据并在刷新时更新.

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.

这篇关于使用 powershell 刷新 Excel 表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！