Excel data model refresh corrupting the data model

[Alan_]

I refresh a number of excel data models using the power bi service. 

As of this week, after the refresh, the model is broken. The result is the workbook crashes (completely closes excel without any error message) when I either try open power pivot or try to open power query on a workbook that has been refreshed via the service. 

[arkinex]

@RosaVB  - I just manually refreshed a couple of reports from the Power BI Service and I can confirm that they are refreshing normally!  I opened up the reports in Excel and everything works fine!  Slicers work and I can go to the Queries and Connection section in the ribbon without it crashing!

I will reachout to my Microsoft Rep to confirm on their end, but I think we are back in business!

I'm not sure how they rollout the updates in the cloud, so I'm wondering how long it will take to affect everyone here.

@Alan_ , @Leione , @EdgarJimenez , @MattyH  - Please give it a try on your end and let us know if its working for you all now!

[rjhale]

I just checked three of my reports that refreshed early this morning, and all three of them are now working as expected.  This is great news, but it would be helpful to know if Microsoft has a plan to stop breaking the Excel data models each time they update the AS Engine.  

[RosaVB]

@arkinex It's still not working for me, I'll have to contact the technical service again 😣

[arkinex]

@RosaVB  - That's not great. Make sure you restored versions of the reports before the issue came back and try again (I would say 07/12/22).  Or you can make a test report with a simple data model and add it to the service.  Schedule the refresh and see if the issue is still present.

Strange that the fix was not rolled out all over.  I wonder if its regional or certain Data Centers got the fix first?

[RosaVB]

Hi @arkinex ! Just the next day everything was fixed, I manually updated the files and they were working.
As you say, updates are probably done geographically and possibly in Europe it has arrived one day later.

Thank you for replying! 🙂

[arkinex]

@RosaVB  - Great news!  Just hoping this stays fixed as @rjhale was saying above!

[ordaj]

I have found that running this script on the Excel file fixes my issue with the data model crashing Excel.  

#some documentation

#https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlfileformat?view=excel-p...

#https://stackoverflow.com/questions/39376896/vba-vbproject-vbcomponents-itemthisworkbook-codemodule-...

#https://docs.microsoft.com/en-us/office/vba/api/excel.application.automationsecurity

#https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa443946(v=vs.60)

function Open-File {

    Add-Type -AssemblyName System.Windows.Forms

    #use your DesktopFolder or w/e

    $initDir = [System.Environment]::GetFolderPath('Desktop')

    $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{InitialDirectory = $initDir}

    #if | Out-Null is not used here, functions returns array with OK and FileName

    $FileBrowser.ShowDialog() | Out-Null

    $filePath = $FileBrowser.FileName

    return $filePath

}

function Release-Ref($ref) {

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) | Out-Null

    [System.GC]::Collect()

    [System.GC]::WaitForPendingFinalizers()

}

$excel = New-Object -ComObject Excel.Application

$excel.Application.EnableEvents = $false

$excel.DisplayAlerts = $false

$excel.Application.AutomationSecurity = 3

$FilePath = Open-File

if(-not (Test-Path -Path $FilePath)) {

    return

}

$code = @"

Sub test()

'

End Sub

"@

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name VBAWarnings -Value 1 -Force | Out-Null

$workbook = $excel.Workbooks.Add($FilePath)

$ListOfModules = @($workbook.VBProject.VBComponents)

foreach ($module in $ListOfModules) {

    if($module.Name -eq 'Test') {

        $TestModuleFound = $true

    } else {

        $TestModuleFound = $false

    }

}

#if module with name 'test' exists we delete 1st 4lines and add $code again which will cause recompile and excel file will work

#if($TestModuleFound) {

#    $xlmodule = $workbook.VBProject.VBComponents.item('Test')

#    $xlmodule.CodeModule.DeleteLines(1,3)

#    $xlmodule.CodeModule.AddFromString($code)

#} else {

    #if module test does not exists we create one and add some lines of dummy code to it which causes project to recompile and file will work

    # .Add(1) for module

    # .Add(2) for class I believe

    # .Add(3) for for UserForm

#    $workbook.VBProject.VBComponents.Add(1).Name = "Test"

#    $xlmodule = $workbook.VBProject.VBcomponents.item('Test')

#    $xlmodule.CodeModule.AddFromString($code);

#}

#save as 52 so it is saved as macro enabled file

#TODO: Excel application is not closing automatically, that should be fixed so we don't have to call stop-process

$workbook.SaveAs($FilePath,52)

$excel.Application.EnableEvents = $true

$excel.DisplayAlerts = $true

$excel.Application.AutomationSecurity = 1

$excel.Workbooks.Close()

$excel.Application.Quit()

$excel.Quit()

#release

Release-Ref($excel)

#quit excel as $excel.Quit() does not do the job or I'm stupid

#Get-Process excel | Stop-Process

Write-Output "Fixed."