PowerShell后台刷新含OLAP查询的Excel时的凭据问题

本人刚接触PowerShell脚本开发，还请谅解。我拥有多个包含连接Power BI数据集的OLAP查询的Excel文件，使用的脚本如下：

$libraryPath = "C:\Repos\AUSD\3.0\Test"
$excel = new-object -comobject Excel.Application
$excel.Visible = $false
# Give delay to open
Start-Sleep -s 3
$allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xls*”
foreach ($file in $allExcelfiles) {
    $workbookpath = $file.fullname
    Write-Host "Updating " $workbookpath
    # Open the Excel file
    $excelworkbook = $excel.workbooks.Open($workbookpath)
    $connections = $excelworkbook.Connections
    # This will Refresh All the pivot tables data.
    $excelworkbook.RefreshAll()
    # The following script lines will Save the file.
    $excelworkbook.Save()
    $excelworkbook.Close()
    Write-Host "Update Complete " $workbookpath
}
$excel.quit()

当设置$excel.Visible = $true时脚本运行正常，但因需在服务器后台定时执行，设置$excel.Visible = $false时出现错误。推测是Excel未可视化打开时自动登录失败导致，请问如何设置凭据或权限以解决该问题？

这个问题我碰到过很多次，核心原因就是无界面模式下的Excel没法触发交互式的凭据验证弹窗，也没法自动复用当前用户的会话凭据。给你几个从易到难的解决方案，你可以根据自己的场景选：

1. 预先保存Excel连接的凭据（最简单的方案） #

先在服务器上用执行脚本的用户账号手动打开一次每个目标Excel文件，然后编辑Power BI的OLAP连接：

• 右键数据透视表/连接，选择「连接属性」
• 切换到「定义」或「凭据」选项卡（不同Excel版本位置略有区别）
• 勾选「保存密码」或者选择「使用我的凭据并保存」，确认后保存文件

这样后续脚本后台运行时，Excel会直接复用预先存好的凭据，不用再弹出登录窗口。

2. 在脚本中显式配置连接凭据 #

如果不方便手动逐个配置文件，可以在脚本里遍历每个连接，直接设置凭据（注意：硬编码密码不安全，建议配合凭据管理器使用）：

$libraryPath = "C:\Repos\AUSD\3.0\Test"
$excel = new-object -comobject Excel.Application
$excel.Visible = $false
Start-Sleep -s 3
$allExcelfiles = Get-ChildItem $libraryPath -recurse -include "*.xls*"

foreach ($file in $allExcelfiles) {
    $workbookpath = $file.fullname
    Write-Host "Updating " $workbookpath
    $excelworkbook = $excel.workbooks.Open($workbookpath)
    $connections = $excelworkbook.Connections

    # 遍历每个连接，针对Power BI的OLAP连接设置凭据
    foreach ($conn in $connections) {
        # 判断是否是Power BI的OLAP连接
        if ($conn.Type -eq 2 -and $conn.OLEDBConnection.ConnectionString -match "PowerBI") {
            $conn.OLEDBConnection.SavePassword = $true
            # 这里推荐从凭据管理器读取，示例用硬编码（不推荐生产环境用）
            $conn.OLEDBConnection.UserID = "你的Power BI账号"
            $conn.OLEDBConnection.Password = "你的密码"
            $conn.Refresh()
        }
    }

    $excelworkbook.Save()
    $excelworkbook.Close()
    Write-Host "Update Complete " $workbookpath
}
$excel.quit()

如果要安全存储密码，可以安装CredentialManager模块，用Get-StoredCredential读取预先保存的凭据：

# 先安装模块（仅需执行一次）
Install-Module -Name CredentialManager -Force

# 读取凭据
$cred = Get-StoredCredential -Target "PowerBI_Dataset"
$conn.OLEDBConnection.UserID = $cred.UserName
$conn.OLEDBConnection.Password = $cred.GetNetworkCredential().Password

3. 调整服务器的DCOM权限 #

如果上面的方法都不行，可能是服务器的DCOM配置限制了无界面Excel的权限：

1. 打开「组件服务」（运行dcomcnfg命令）
2. 依次展开「组件服务 > 计算机 > 我的电脑 > DCOM配置」
3. 找到「Microsoft Excel应用程序」，右键选择「属性」
4. 在「安全」选项卡：
   • 给执行脚本的用户分配「启动和激活权限」和「访问权限」的允许权限
5. 在「标识」选项卡：
   • 选择「交互式用户」或者直接指定执行脚本的用户账号，确保该账号有权访问Power BI数据集

4. 替代方案：绕过Excel，直接用Power BI API刷新 #

如果你的核心需求只是刷新Power BI数据集，其实完全没必要依赖Excel COM对象，用Power BI REST API更稳定，也更适合后台定时任务：

# 配置参数
$tenantId = "你的租户ID"
$clientId = "你的应用注册ID"
$clientSecret = "你的应用密钥"
$groupId = "你的工作区ID"
$datasetId = "你的数据集ID"

# 获取访问令牌
$tokenBody = @{
    Grant_Type    = "client_credentials"
    Scope         = "https://analysis.windows.net/powerbi/api/.default"
    Client_Id     = $clientId
    Client_Secret = $clientSecret
}
$tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -Method Post -Body $tokenBody
$accessToken = $tokenResponse.access_token

# 触发数据集刷新
$refreshUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"
Invoke-RestMethod -Uri $refreshUri -Method Post -Headers @{Authorization = "Bearer $accessToken"}

这个方法彻底避开了Excel COM对象的各种坑，是后台定时任务的最优解。

内容的提问来源于stack exchange，提问作者zuheir