VBA - Simple save as .xls

[Chanty]

Sorry if not listing this correctly, as not been on for a few years and my old account has gone.

Been trying to get a really simple bit of code to save the file I have to .xls format
No name or directory change, as save exactly as it is, but in .xls

Thought the following would work, but keep getting an error!

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName.xls

 

[Joe4]

Welcome to the Board!

Try this:

    Dim fName As String
    fName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fName, FileFormat:= _
        xlExcel8, CreateBackup:=False

 

[Skyybot]

Here's a simple one if placed in ThisWorkbook module. Can be edited to save with a Sheet Name or value of a Cell.

Option Explicit

Sub SaveAsXLS()
Application.DisplayAlerts = False
'Replace [Name of your File] with what you want your FileName to be (without brackets) i.e. - Birds
Me.SaveAs Filename:=Me.Path & "\[Name of your File].xls", FileFormat:=56
Application.DisplayAlerts = True
End Sub

 

[Chanty]

Welcome to the Board!

Try this:

    Dim fName As String
    fName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fName, FileFormat:= _
        xlExcel8, CreateBackup:=False

Thanks Joe
Saved as an xls, but in an odd location - I even tried changing 'ThisWorkbook' to 'ActiveWorkbook', which saved in a less odd location, but not the one where the original file is located!
Any thoughts very much welcome - many thanks.

 

[Chanty]

Here's a simple one if placed in ThisWorkbook module. Can be edited to save with a Sheet Name or value of a Cell.

Option Explicit

Sub SaveAsXLS()
Application.DisplayAlerts = False
'Replace [Name of your File] with what you want your FileName to be (without brackets) i.e. - Birds
Me.SaveAs Filename:=Me.Path & "\[Name of your File].xls", FileFormat:=56
Application.DisplayAlerts = True
End Sub

Thanks Skyybot
Getting an error on the second 'me' but also just want the file to be the same name, as will use on multiple files and will add this to the end of other code I'm using to tidy up the files, save as xls and close.
Many thanks for your help.

 

[Alex Blakenburg]

I take it that the code is not in the workbook that you are trying to save. ThisWorkbook refers to the workbook that the code is in.
When you used ActiveWorkbook where did it go, ie does a "less odd location" mean ?
Is the original file on a OneDrive or Sharepoint folder ?

 

[Chanty]

I take it that the code is not in the workbook that you are trying to save. ThisWorkbook refers to the workbook that the code is in.
When you used ActiveWorkbook where did it go, ie does a "less odd location" mean ?
Is the original file on a OneDrive or Sharepoint folder ?

Hi Alex
The code is kept in the Excel 'Personal' file, so not in the actually workbook I will be working on, just hidden in the background.
Less odd location, was just in my user folder - file I am working on is in a folder in my documents folder.

 

[Alex Blakenburg]

It shouldn't make any difference but since you are using a Mac and we can't replicate the issue give it a try anyway.

    Dim fName As String
    fName = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, ".")) & "xls"
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:= _
        xlExcel8, CreateBackup:=False

 

[Chanty]

It shouldn't make any difference but since you are using a Mac and we can't replicate the issue give it a try anyway.

    Dim fName As String
    fName = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, ".")) & "xls"
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:= _
        xlExcel8, CreateBackup:=False

Thanks Alex
Worked a treat!
Many thanks for your help - much appreciated.

 

[Alex Blakenburg]

No worries. Thanks for letting us know.