VBA file name has period but want file format recognised as xlsm or 53

[c hassett]

Hi I'm writing some VBA code to take the value of cell C7 and copy the sheet into a new workbook, unless C7 is blank.
The problem is that the file name from C7 has a period in it that is seen as the start of the file type. The period needs to stay!

When the process of taking the value of C7 and saving the sheet as a new workbook is done manually the file is recognised as an excel file. Just can't figure out how to code it in VBA? I'm new to writing code.

'Code

Sub Save_n_Name()
Dim ws As Worksheet
' ws stands for Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Turn off alerts and stop updating the screen / calculations
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
'Copy each worksheet in the active workbook
If ws.Range("C7").Value <> "" Then
'If cell C7 on the sheet has a value then do the following if it equals blank or zero then do nothing
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Range("C7").Value, FileFormat:=53
End If
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Any help gratefully received,
Carl

 

[daverunt]

Hi,

See if this works: Note xlsm =52

'Copy each worksheet in the active workbook
[COLOR="#FF0000"]extn=".xlsm"[/COLOR]
 If ws.Range("C7").Value <> "" Then
 'If cell C7 on the sheet has a value then do the following if it equals blank or zero then do nothing
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Range("C7").Value [COLOR="#FF0000"]& extn[/COLOR], FileFormat:=[COLOR="#FF0000"][B]52[/B][/COLOR]

 

[c hassett]

That worked. Thanks daverunt

The code amended itself to the below, looks like it spaced itself:

'Copy each worksheet in the active workbook
extn = ".xlsm"
If ws.Range("C7").Value <> "" Then
'If cell C7 on the sheet has a value then do the following if it equals blank or zero then do nothing
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Range("C7").Value & extn, FileFormat:=52