VBA code fails on SaveAs when .xltm

[spydey]

Morning everyone!!,

This is my first post here. I normally stay back and figure things out on my own but this one has me stumped. Any help or insight that you might be able to provide is greatly appreciated!!

I have some VBA code that I use to save a workbook. It is linked to a button for ease of use. It currently is in an .xlsm formatted workbook. However, I and several others will need to access the file and save our own copies each time we use it. Thus I would like to make the file a macro-enabled template (.xltm).

Below is my code:

Sub Save_Completed_Review()


Dim Rep As Variant
Dim Path As String
Dim filename As String
Dim SaleDate As Date
SaleDate = Range("B4").Value
Rep = InputBox("Please input the name of the Rep here:", "Rep Name")
Path = ActiveWorkbook.Path & "\" & Year(Date) & "\" & Format(SaleDate, "mm") & " - " & Format(SaleDate, "mmm") & "\" & Rep & "\Completed\"
filename = Range("B2").Value & " - " & Range("B12").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=Path & filename, FileFormat:=51
Application.DisplayAlerts = True

As a normal .xlsm, the code works great!! No issues. But if I save the .xlsm file to a .xltm via the normal File menu --> SaveAs option, then try to use the macro, the code breaks and I cannot figure out why.

I get an error code 400, which as I understand it is just a generic error code indicating that something went wrong but the system can't really determine what, right?

Again, any insight or assistance you can provide is greatly appreciated.

Thanks and take care everyone!

-Spydey

P.S., I am running Excel 2013.

 

[spydey]

Sorry everyone, I missed inputting the "End Sub" in my code above, but it is in my actual code in Excel. I apologize for any confusion. Still having issues though.

-Spydey

 

[RoryA]

Welcome to the posting world.

If you are using a template, then ActiveWorkbook.Path will be blank since a new unsaved workbook is created when you use the template. I imagine that is the problem.

 

[spydey]

@ RoryA,

Mr. RoryA, sir, you are a genius!! I am almost positive you are correct. I am going to have to give an absolute path for my save location instead of the relative path via the ActiveWorkbook.Path.

It was so obvious! Thank you so much for your assistance. I will report back and let you know what I come up with. Thank you for your quick response.

-Spydey

 

[RoryA]

Glad to help.

 

[spydey]

I changed the relative path to an absolute path, and it worked great! Thanks again RoryA!

-Spydey

 

[get2guy]

Hi Spydey and RoryA,

I think i have a similar issue. can you please help me?
This is my post: https://www.mrexcel.com/forum/excel...ttachment-outlook-message.html?highlight=xltm