Create a folder on a users desktop and then save the file as xlsx not xlsm (Remove macros)

[TkdKidSnake]

Hi all,

Can you help me, I am trying to create a folder called "JJS Submission Docs" on a users desktop however the use is not defined and then save the particular file in this folder as an xlsx file instead of the original xlsm as I do not want the macros in the newly saved version. What I have so far is below but unfortunately it doesn't work:

Sub SaveToDeskTop()

Application.DisplayAlerts = False

Dim usrnme As String
Dim myFolderName As String
Dim myFileName As String

'Get username
usrnme = Environ("username")

Sheets("Level 3 PPAP Requirements").Select

    ActiveSheet.Unprotect
    
    Range("G4").Select
    Selection.Copy
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G6").Select
    
'   Build folder name with today's date
    myFolderName = Environ("username") & "\JJS Submission Docs\"

'   Check to see if folder name exists already.  If not, create it
    If (Dir(myFolderName, vbDirectory)) = "" Then MkDir myFolderName

'   Build file name - the filename is in cell i1 on tab Level 3 PPAP Requirements"
    myFileName = Range("i1") & ".xlsx"
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False

Sheets("1. PSW").Select

'   Save file
    ActiveWorkbook.SaveAs Filename:=myFolderName & "\" & myFileName, _
        FileFormat:=51, CreateBackup:=False

Application.DisplayAlerts = True

End Sub

If anyone can help me with this it would be greatly appreciated.

Many thanks.

 

[Fluff]

Try

myFolderName = Environ("userprofile") & "\Desktop\JJS Submission Docs\"

 

[TkdKidSnake]

Try

myFolderName = Environ("userprofile") & "\Desktop\JJS Submission Docs\"

Many thanks for this it then jump to another error but that was pretty simple to solve as it was the folder location with filename had an - & "\" -
and it didn't need it.

Code used is below for reference:

Sub SaveToDeskTop()

Application.DisplayAlerts = False

Dim usrnme As String

'Get username
usrnme = Environ("username")

Sheets("Level 3 PPAP Requirements").Select

    ActiveSheet.Unprotect
    
    Range("G4").Select
    Selection.Copy
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G6").Select
    
Dim myFolderName As String
    Dim myFileName As String
    
'   Build folder name with today's date
    myFolderName = Environ("userprofile") & "\Desktop\JJS Submission Docs\"


'   Check to see if folder name exists already.  If not, create it
    If (Dir(myFolderName, vbDirectory)) = "" Then MkDir myFolderName


'   Build file name
    myFileName = Range("i1") & ".xlsx"
    

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False

Sheets("1. PSW").Select

'   Save file
    ActiveWorkbook.SaveAs Filename:=myFolderName & myFileName, _
        FileFormat:=51, CreateBackup:=False
Dim FileName1 As String


Application.DisplayAlerts = True


End Sub

 

[Fluff]

You're welcome & thanks for the feedback.