I have a functional macro that saves my current file (format as xlsm or xlsx) as the same file type with today's date. The macro also has a Browse for folder functionality. Sol I am sure you can imagine, I use this constantly. Okay, I now use a lot of .xlsb files as well, so I would like this macro to function on any of the 3 file types. I am not familiar with IF statements for macros, so I am not sure how to incorporate this new functionality. Thanks so much in advance for your help!!!!
' Saves File as current filename with date listed in format below (.xlsm or .xlsx)
Dim xWB As Workbook
Dim xStr As String
Dim xStrOldName As String
Dim xStrDate As String
Dim xFileName As String
Dim xFileDlg As fileDialog
Dim i As Variant
Application.DisplayAlerts = False
Set xWB = ActiveWorkbook
xStrOldName = xWB.Name
xStr = Left(xStrOldName, Len(xStrOldName) - 5)
xStrDate = Format(Now, "MM-DD-YYYY") ' Date Format
If Right(xStrOldName, 4) = "xlsm" Then
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
Else
xFileName = Application.GetSaveAsFilename(xStr & " - " & xStrDate, "Excel Workbook (*.xlsx),*.xlsx")
End If
xWB.SaveAs (xFileName)
Application.DisplayAlerts = True
End Sub
I suggest you take a look here.
This link tells you about the different fileformats and will give you an idea how to handle your question
https://www.rondebruin.nl/win/s5/win001.htm
@Hall Hallebeek,
I thought I got it, because it saved my xlsx file as the same. But when I tried on an xlsb file, it "chose" xlsx as the file type, I cancelled the process and the file saved as false.xlsb, the same is true of xlsm. It saves everything as an xlsx. I don't understand what I did wrong.
' Saves File as current filename with date listed in format below (.xlsm or .xlsx)
Dim xWB As Workbook
Dim xStr As String
Dim xStrOldName As String
Dim xStrDate As String
Dim xFileName As String
Dim xFileDlg As fileDialog
Dim i As Variant
Application.DisplayAlerts = False
Set xWB = ActiveWorkbook
xStrOldName = xWB.Name
xStr = Left(xStrOldName, Len(xStrOldName) - 5)
xStrDate = Format(Now, "MM-DD-YYYY") ' Date Format
If Right(xStrOldName, 52) = "xlsm" Then
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
Else
If Right(xStrOldName, 50) = "xlsb" Then
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Binary Workbook (*.xlsb),*.xlsb")
Else
xFileName = Application.GetSaveAsFilename(xStr & " - " & xStrDate, "Excel Workbook (*.xlsx),*.xlsx")
End If
End If
xWB.SaveAs (xFileName)
Application.DisplayAlerts = True
End Sub
strOldNAme does NOT tell you which filetype it is evenb if you selectv the extension
Query the filetype, not the extension
Your code checks the last 52 characters to see if they match "xlsm" (which they can't), then the last 50 characters to see if they match "xlsb" (again, they can't) then defaults to xlsx. You could do something like this:
Sub SaveAsToday()
' Saves File as current filename with date listed in format below (.xlsm or .xlsx)
Dim xWB As Workbook
Dim xStr As String
Dim xStrOldName As String
Dim xStrDate As String
Dim xFileName As String
Dim xFileDlg As FileDialog
Dim i As Variant
Application.DisplayAlerts = False
Set xWB = ActiveWorkbook
xStrOldName = xWB.Name
xStr = Left(xStrOldName, Len(xStrOldName) - 5)
xStrDate = Format(Now, "MM-DD-YYYY") ' Date Format
Dim currentFormat As XlFileFormat
currentFormat = xWB.FileFormat
Select Case currentFormat
Case xlOpenXMLWorkbookMacroEnabled
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
Case xlExcel12
xFileName = Application.GetSaveAsFilename(xStr & " " & xStrDate, "Excel Binary Workbook (*.xlsb),*.xlsb")
Case xlOpenXMLWorkbook
xFileName = Application.GetSaveAsFilename(xStr & " - " & xStrDate, "Excel Workbook (*.xlsx),*.xlsx")
Case Else
' up to you what to do here
End Select
xWB.SaveAs Filename:=xFileName, FileFormat:=currentFormat
Application.DisplayAlerts = True
End Sub
@Velouria,
Works perfectly. And thanks for taking the time to explain it to me, now I get it. Appreciate all your help!
