Solved: ActiveWorkbook.SaveAs xlsm help!

[Andybuck86]

You guys have helped me out plenty of times in the past and I'm hoping you can do the same again for me!

I have the following code as a module in my workbook

[VBA]Option Explicit
Sub Save_Password()
Application.DisplayAlerts = False
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Sheets("QryData").Range("J32").Value & "\" & Sheets("QryData").Range("J31").Value & ".xlsm"
Open_Password = InputBox("Please enter a password")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password

End Sub
[/VBA]

This worked great until I decided I wanted to save my spreadsheet as a template. After this when I run my code I get a run-time error 1004

I have searched forums and think I need to add something like this :

fileformat:=52
52 = xlOpenXMLWorkbookMacroEnabled

But I keep getting compile errors...

Any ideas?

[MCraiss]

It seems to me, that this has something to do with access rights.

When I execute the following command on my Win7 machine I also get an error 1004:
ActiveWorkbook.SaveAs "c:\test.xlsm", 52

If I change the path to some directory where you don't need administrative rights to save/delete files the same code works without trouble:

ActiveWorkbook.SaveAs "c:\data\test.xlsm", 52

This happens although I am logged in as administrator on my machine!
May be you could try to change your path too.

Hope this helps

Michael

[mancubus]

here is file formats...

File Format Name - FileFormat Value
Excel 2007-2010 Binary Workbook (.xlsb) - 50
Excel 2007-2010 Workbook (.xlsx) - 51
Excel 2007-2010 Macro-Enabled Workbook (.xlsm) - 52
Excel 2007-2010 Macro-Enabled Template (.xltm) - 53
Excel 2007-2010 Template (.xltx) - 54
Excel 2007-2010 Add-In (.xlam) - 55
Excel 1997-2003 Workbook (.xls) - 56

if you do not specify a folder, default location for templates is:
C:\Users\User_Name\AppData\Roaming\Microsoft\Templates
or
C:\Documents and Settings\user_name\Application Data\Microsoft\Templates


if you are trying to save to a network folder, as Michael says, you may not have access to that folder.

[mdmackillop]

Michael,
That is default behavior since 2007 for MS "security" reasons

[Andybuck86]

Thanks all for the responses.

I don't believe this has to do with security problems. Yes I am saving to a network drive and security problems was my initial thought.

But I have tested saving this to my desktop and still have the same problems.

Also I am able to save to the network drive through FILE>SAVE AS without any issues. I tested it with the same name and file extension that I am using in my vba and it works.

The file extension I need is Excel 2007-2010 Macro-Enabled Workbook (.xlsm) - 52

I'm not sure where to build this into my code though - I have tried but keep getting compile errors (I am a noob!). My current code is as follows:

[vba]Option Explicit
Sub Save_Password()
Application.DisplayAlerts = False
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Sheets("QryData").Range("J32").Value & "\" & Sheets("QryData").Range("J31").Value
Open_Password = InputBox("Please enter a password")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password

End Sub
[/vba]

Any help would be massively appreciated

[Andybuck86]

Also I should mention that if the workbook is orignally in XLSM format then my code works. The error is if I save the workbook as a macro enabled template and then use my code to save as XLSM

Thanks again

[Andybuck86]

Ok I have it working now thanks to mancubus. For anyone interested my code is now as follows:

[vba]Option Explicit

Sub Save_Password()

Application.DisplayAlerts = False

Dim Open_Password As String
Dim New_File_Name As String

New_File_Name = Sheets("QryData").Range("J32").Value & "\" & Sheets("QryData").Range("J31").Value

Open_Password = InputBox("Please enter a password")

ActiveWorkbook.SaveAs New_File_Name, _
FileFormat:=52, _
WriteResPassword:=Open_Password, _
Password:="test"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' FileFormat:=52, _ ' xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password

End Sub
[/vba]

Thanks all for the help