excel ExportAsFixedFormat does not work in .xls when updated to windows 11

[Yveske]

Hi,

I had some VBA code which creates a pdf from an excel file (.xls).
This worked without a problem.
Since yesterday my computer is updated to windows 11 and now this code does not work anymore.
When I open the .xls file, it gives an error on the line with 'ExportAsFixedFormat'.
When I save my file as .xlsx and then run the code, then there is no error?
Does anyone have an idea why this doens't work in my .xls file?
Maybe a setting in windows ?

Thanks

 

[Joe4]

Welcome to the Board!

Please post your VBA code so we can try to analyze it.
See here for how to post your VBA code in a manner that makes it easy to read and copy: How to Post Your VBA Code

 

[Yveske]

Hereby the VBA code
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sBestand & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

But the code worked in excel with extension ".xls" in windows 10, but not in windows 11.
For extension ".xlsx" the code is still working in windows 10 and windows 11.

 

[Joe4]

Please post the ENTIRE VBA procedure, as you have a lot of variables referenced in there.

And what is the EXACT error message?

 

[Yveske]

sPath & sBestand & ".pdf" ==> c:\[username]\Details_yyyymmddhhnnss.pdf

 

[Yveske]

the error code is: -2147467259

 

[Joe4]

If you don't give us what we ask, it is very hard to help! We need you to work with us here.

Please post the ENTIRE VBA procedure, as you have a lot of variables referenced in there.

And what is the full, exact error message (not just the number)?

 

[Yveske]

Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase (cstDBPATH)
acc.Visible = False

Application.ScreenUpdating = False
Application.StatusBar = False
Application.Cursor = xlWait

sPath = acc.dlookup("fldPath", "tblFolderSupplement", "fldDienstfiches=True AND #" & Format(dDate, "yyyy/mm/dd") & "# Between fldVan and fldTot") & ""
If sPath = "" Then
MsgBox "Geen folder gevonden in DB voor datum '" & Format(dDate, "yyyy/mm/dd") & "' ." & vbCrLf & cstContactWho, vbCritical + vbOKOnly, cstAppName
bErr = True
GoTo End_Handler
End If

If Dir(sPath, vbDirectory) = "" Then
Shell ("cmd /c mkdir """ & sPath & """")
End If
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"

iCount = wbThis.Worksheets.Count
i = 0
For Each ws In wbThis.Worksheets
i = i + 1
Application.StatusBar = "Sheet " & i & " of " & iCount & " finished."
If InStr(1, ws.Name, "(") > 0 Then
ws.Name = Left(ws.Name, InStr(1, ws.Name, "(") - 2)
End If
sBestand = ws.Name
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sBestand & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next

When on line 'ws.ExportAsFixedFormat ...' it throws below error message

Error -2147467259 (80004005) during execution:
The document is not saved.

Thanks

 

[Joe4]

You have a structural error in your For/Each block. You have an "End If" with no corresponding "If".
This is easier to see if you structure/format your VBA code.

For Each ws In wbThis.Worksheets
    i = i + 1
    Application.StatusBar = "Sheet " & i & " of " & iCount & " finished."
    If InStr(1, ws.Name, "(") > 0 Then
        ws.Name = Left(ws.Name, InStr(1, ws.Name, "(") - 2)
    End If
    sBestand = ws.Name
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sBestand & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next

I am using color blue to show an IF with its matching END IF.
But you can see that the red END IF has no matching IF.

You will get an error without even running the code if you try compiling it first (Debug -> Compile VBA Project).

The error message will say:
"Compile error: End If without block if".

 

[Yveske]

My mistake.
The correct code is below. The last END IF was not in my original code.

Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase (cstDBPATH)
acc.Visible = False

Application.ScreenUpdating = False
Application.StatusBar = False
Application.Cursor = xlWait

sPath = acc.dlookup("fldPath", "tblFolderSupplement", "fldDienstfiches=True AND #" & Format(dDate, "yyyy/mm/dd") & "# Between fldVan and fldTot") & ""
If sPath = "" Then
MsgBox "Geen folder gevonden in DB voor datum '" & Format(dDate, "yyyy/mm/dd") & "' ." & vbCrLf & cstContactWho, vbCritical + vbOKOnly, cstAppName
bErr = True
GoTo End_Handler
End If

If Dir(sPath, vbDirectory) = "" Then
Shell ("cmd /c mkdir """ & sPath & """")
End If
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"

iCount = wbThis.Worksheets.Count
i = 0
For Each ws In wbThis.Worksheets
i = i + 1
Application.StatusBar = "Sheet " & i & " of " & iCount & " finished."
If InStr(1, ws.Name, "(") > 0 Then
ws.Name = Left(ws.Name, InStr(1, ws.Name, "(") - 2)
End If
sBestand = ws.Name
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sBestand & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next