Excel vba save as using original file format

0
votes

I am writing a macro that will open a template workbook, make some changes and then do a Save As to a new name. Sometimes the template workbook will be xls, sometimes xlsx. What do I use as the fileformat parameter so that I save the new file using the same file format as the original template file? The examples I see all use a specific file format which in my case might be different depending on what the original file format is. I am using Excel 2010.

2
vbaexcel
What have you tried so far? Worst-case scenario this is basic string concatenation. Show your code, explain where it is not doing what you want, and we'll be happy to help... – David Zemens
blogs.office.com/2009/07/07/… - Should give you what you're looking for – John Bustos
I tried using specific fileformat #s but obviously, they save as the format indicated. I found a reference to xlNormal but that always saved as an xls. Here is the code I am trying to use: ActiveWorkbook.SaveAs _ FileName:=OutputPath & NewName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False – Roger B.
Did you try the code in the link?? – John Bustos

2 Answers

1
votes

This function will return the file extension including the leading period.

Function FileExtension(Fname As String)
    FileExtension = Right(Fname, Len(Fname) - InStrRev(Fname, ".") + 1)
End Function

which could be used in your code:

ActiveWorkbook.SaveAs FileName:=OutputPath & NewName & _
    FileExtension(ActiveWorkbook.Name)

I'm assuming:

  • OutputPath ends with a "\"
  • FileName does not have an extension or a trailing period
0
votes

what you know about the problem already actually makes the string handling, as described by @DavidZemens, pretty easy to implement. Below is a function called GetTheFileExtension that takes a Workbook and returns a string with the file extension of said Workbook.

Add an If statement to save your file based on the returned string and you should be good to go:

Option Explicit
Public Function GetTheFileExtension(TargetWorkbook As Workbook) As String
    Dim sFullName As String, sTemp As String
    sFullName = TargetWorkbook.FullName '<~ grab the full file path
    sTemp = Right(sFullName, 4) '<~ we passed in a workbook, so only interested in the last 4
    If InStr(1, sTemp, ".", vbTextCompare) Then
        GetTheFileExtension = Right(sTemp, 3) '<~ if there's a period, we know it's .xls
    Else
        GetTheFileExtension = sTemp '<~ if not, we know it's xlsx
    End If
End Function

And here's a little test that will MsgBox the extension:

Sub TestThatFunction()

Dim sFileExtension As String

'test the function by assigning it to a string
sFileExtension = GetTheFileExtension(ThisWorkbook)

'msgbox to check the results
MsgBox (sFileExtension)

'add an If statement to your code
'that saves the file in one manner
'if the file extension is "xls"
'and in the other manner if the
'extension is "xlsx"

End Sub