Export to excel causes corrupted file after office updgrade

[BryanRodriguesSamuel]

Hello all.



We recently upgrading office professional plus 2013, and a previous DB app that worked now causes excel to open with blank / grey screen.

It is happening on my formatexcel sub, but I've included all the code in case it is because of the file format/version.

All worked perfectly before the upgrade, i had to modify the code for the current excel version. Code runs without any errors, and file size indicates there is data in the workbook, but when opened, there are no tabs/data, just a grey screen.

Any help would be appreciated.

Thank you

Sub ExportToXlsxInventoryMetrics(strWHSE As String, strInventoryGroup As String)
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Dim myMonth As String
    Dim myDay As String
    Dim myYear As String
    Dim xlsxPath As String
    Dim StrSQL As String
    
    myMonth = DatePart("m", Now())
    myDay = DatePart("d", Now())
    myYear = DatePart("yyyy", Now())
    
    If Not strInventoryGroup = "All" Then
        StrSQL = "Select * From VIEW_INVENTORY_METRICS_CROSSTAB Where (WHSE = '" & strWHSE & "' and Category ='" & strInventoryGroup & " ')"
    Else
        StrSQL = "Select * From VIEW_INVENTORY_METRICS_CROSSTAB Where WHSE = '" & strWHSE & "'"
    End If
    
    xlsxPath = "\\ReportServer\Reports\Stock Code Metrics\" & strInventoryGroup & " Supply Chain Metrics " & myMonth & "_" & myDay & "_" & myYear & ".xlsx"




    Set qdf = cdb.CreateQueryDef(strWHSE, StrSQL)
            
    Set qdf = Nothing
    DoCmd.TransferSpreadsheet acExport, 10, strWHSE, xlsxPath, True
    
       
    DoCmd.DeleteObject acQuery, strWHSE
    Set cdb = Nothing
    Call FormatExcel(xlsxPath, strWHSE)
End Sub






Sub FormatExcel(strFile As String, strSheet As String)
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim lastrow As Long
Dim Rng As Range
MySheetPath = strFile
Debug.Print strFile
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)


Xl.Visible = True




Set XlSheet = XlBook.Worksheets(strSheet)
    With XlSheet
        .Range("A1").EntireRow.Insert xlDown
        .Range("A1").FormulaR1C1 = strSheet & " WHSE Metrics and 12 Month Usage"
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Size = 16
        .Range("AB2").FormulaR1C1 = "6 Mo Avg"
        .Range("AB3").Formula = "=SUM(V3:AA3)/6"
        .Range("AB3").NumberFormat = "0.0"
        .Range("AC2").FormulaR1C1 = "12 Mo Avg"
        .Range("AC3").Formula = "=SUM(P3:AA3)/12"
        .Range("AC3").NumberFormat = "0.0"
        .Range("AD2").FormulaR1C1 = "Months On Hand"
        .Range("AD3").Formula = "=IF(M3=0,0,IF(ISERROR(M3/AB3),0,M3/AB3))"
        .Range("AD3").NumberFormat = "0.0"
        .Range("AE2").FormulaR1C1 = "Ext'd OH Value"
        .Range("AE3").Formula = "=M3*H3"
        .Range("AE3").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        .Range("AF2").FormulaR1C1 = "Ext'd 6 Mo Avg Value"
        .Range("AF3").Formula = "=AB3*H3"
        .Range("AF3").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        .Range("AG2").FormulaR1C1 = "No Movement 12 Months"
        .Range("AG3").Formula = "=IF(AC3=0,M3,0)"
        .Range("AH2").FormulaR1C1 = "Over 6 Mo OH"
        .Range("AH3").Formula = "=IF(AB3=0,AE3,IF(AD3>5.99,(AE3-AF3),0))"
        .Range("AH3").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        .Range("AB3:AH3").Copy
        
        .Range("AB4:AH" & lastrow).PasteSpecial xlPasteFormulasAndNumberFormats
        .Range("A2:AH2").Font.Bold = True
        .Range("A2:AH2").Interior.ColorIndex = 33
        .Rows("2:2").RowHeight = 24.75
        .Columns("A:AH").EntireColumn.AutoFit
        .Columns("A:A").ColumnWidth = 7.29
        
            Set Rng = .Range("AG4:AG" & lastrow)
            Rng.FormatConditions.Delete
            Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
            Rng.FormatConditions.Item(1).Interior.Color = 8420607
            Set Rng = .Range("AD4:AD" & lastrow)
            Rng.FormatConditions.Delete
            Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=6"
            Rng.FormatConditions.Item(1).Interior.Color = 8420607
            Set Rng = .Range("AH4:AH" & lastrow)
            Rng.FormatConditions.Delete
            Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
            Rng.FormatConditions.Item(1).Interior.Color = 8420607
        
        
    End With
XlBook.Save
XlBook.Close
Xl.Quit
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

[BryanRodriguesSamuel]

Forgot to mention.

If i do not run the formatexcel sub, the file can be opened normally.

[BryanRodriguesSamuel]

Alright...

If anybody else experiences this, i know what the problem is...

the workbook is being hidden and view>unhide fixes the problem.