VBA .xlsm SaveAs .xlsx with FileFormatNum = 51 Not Working

[Shiseiji]

Thanks for looking. Question is why is the first line below givers the "This extension can not be used with the selected file type. " error and the second line works?

ThisWb.SaveAs fileName:=wbDest & ThisWbName & FileExtStr ThisWb.SaveAs fileName:=wbDest & ThisWbName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Sub m_SaveWBasXLSX()
  ' 20210721
  ' https://www.rondebruin.nl/win/s5/win001.htm
  With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .DisplayStatusBar = False
  End With

  Dim FileFormatNum As Long
  Dim dt As String
  Dim FileExtStr As String
  Dim ThisWbName  As String
  Dim ThisWb  As Workbook
  dt = Format((Now), "yyyy-mm-dd_hmmAM/PM")
  Set ThisWb = ActiveWorkbook
'--
  FileExtStr = ".xlsx": FileFormatNum = 51
'  FileExtStr = ".xlsm": FileFormatNum = 52
'  FileExtStr = ".xlsx": FileFormatNum = 51
'  FileExtStr = ".xls": FileFormatNum = 56
'  FileExtStr = ".xlsb": FileFormatNum = 50
'  FileFormat:=xlOpenXMLWorkbook
'--
  FileExtStr = ".xlsx": FileFormatNum = 51
  ThisWbName = "AD_Listing_" & dt
  wbDest = "S:\Security (Restricted)\SECURITY SERVICES CENTER DOCUMENTATION\AD_Listing\"
' Error - This extension can not be used with the selected file type. Change the file in the File name box or select different file type by changing the Save as type. _
' ThisWb.SaveAs fileName:=wbDest & ThisWbName & FileExtStr
  ThisWb.SaveAs fileName:=wbDest & ThisWbName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'
End Sub

TIA

Ron

 

[johnnyL]

You didn't tell it to SaveAs FileFormat:=51 ... 51 = xlsx

Try:

ThisWb.SaveAs fileName:=wbDest & ThisWbName & FileExtStr, FileFormatNum

Or

ThisWb.SaveAs fileName:=wbDest & ThisWbName & FileExtStr, FileFormat:=51

 

[Shiseiji]

Thanks. Other projects got shoved at me, I apologize for the delay. This was a bit of a rabbit warren as I had used:

& ".xlsx", FileFormat:=51

courtesy of Mr. de Bruin for years and then saw the use of "FileExtStr" and "wbDest" and thought it looked interesting. Obviously I couldn't get the pieces put together correctly.

Thanks again!

Ron

 

[johnnyL]

You are welcome @Shiseiji.