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

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
172
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,090
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You didn't tell it to SaveAs FileFormat:=51 ... 51 = xlsx

Try:

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

Or

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

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
172
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Thanks. Other projects got shoved at me, I apologize for the delay. This was a bit of a rabbit warren as I had used:
VBA Code:
& ".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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,573
Messages
5,765,182
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top