0

I am using the following macro to convert an xls file to xlsx. The problem with it is the line that says SaveAs. This creates a second file, one with the original xls extension and the second with the xlsx extension. If I simply say Save, I get an error. How can I either delete the xls file or have the macro do a Save instead of SaveAs? The reason we need to convert the file is so that when we email the file it is smaller in size. 

Dim s As String
s = ActiveWorkbook.FullName
s = Replace(s, "xls", "xlsx")
ActiveWorkbook.SaveAs Filename:=s, _
      FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Degustaf
  • 2,655
  • 2
  • 16
  • 27
Markpb52
  • 1
  • 1
  • There are two separate operations here - Creating a new file (in xlsx format) and deleting an old file. There really isn't a way around this. Sounds like you have the first task covered. Here's some answer related to the second: http://stackoverflow.com/questions/67835/deleting-a-file-in-vba – Adam Nov 21 '14 at 21:13

1 Answers1

1

In terms of saving your current file, you have 2 choices: overwrite your current file (with the same type), or generate a new file. This is a limitation (feature?) of Excel, not VBA.

So, to acheive what you want, we will need to do some more work. What we need for this is the FileSystemObject. This basically allows you to interact with the files and directories on your computer through VBA. The first thing we need to do is keep the original file name; This will allow us to delete it by name. From there, we will create the FileSystemObject, and use it to call the DeleteFile method. So, your code should look like

Dim old_name As String, new_name As String
old_name = ActiveWorkbook.FullName
new_name = Replace(old_name, "xls", "xlsx")
ActiveWorkbook.SaveAs Filename:=new_name, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Dim FSO as FileSystemObject
Set FSO = New FileSystemObject
FSO.DeleteFile old_name
Set FSO = Nothing

Note that you will need to set a reference to Microsoft Scripting Runtime in order to create and use a FileSystemObject.

Degustaf
  • 2,655
  • 2
  • 16
  • 27