Format 51

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
Good Evening

Looking for some assist here. I am saving a workbook whichcontains several worksheets. When the new file is created there are two issues.

1. Message indicating to save the new file as an xlsx not amacro enabled file.

2. The new Worksheet the sheets have been grouped.

How can I avoid each of the above.

The code I am currently working with is:

Code:
Sub SaveE()
Dim Mypath As String
Dim Filename As String
Mypath = Environ("USERPROFILE") & "\Desktop\"
Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
Sheets.Select
ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsx", FileFormat:=51
End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,659
Office Version
365, 2010
Platform
Windows, Mobile
What happens with

Code:
Sub SaveE()
Dim Mypath As String
Dim Filename As String
Mypath = Environ("USERPROFILE") & "\Desktop\"
Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
'Sheets.Select
ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsm", FileFormat:=52
End Sub
not sure why you have the & " " & in there?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,723
Office Version
2007
Platform
Windows
Try this

Code:
Sub SaveE()
  Dim Mypath As String
  Dim Filename As String
[COLOR=#0000ff]  Application.DisplayAlerts = False[/COLOR]
  Mypath = Environ("USERPROFILE") & "\Desktop\"
  Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
  ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsx", FileFormat:=51
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,659
Office Version
365, 2010
Platform
Windows, Mobile
1. Message indicating to save the new file as an xlsx not amacro enabled file
Fileformat 51 is for xlsx.
 

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
The & " " & was used as a spacer in another code of mine, not relevant here.Thank You Mark works perfect.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,723
Office Version
2007
Platform
Windows
@billandrew, youre welcome.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,659
Office Version
365, 2010
Platform
Windows, Mobile
Your saving it as the new file name and extension not creating a new workbook so it is still open but renamed.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,819
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top