Save a Macro Enabled Template to a custom location with a custom file name.

**andcha** · 01-27-2012, 06:30 AM

Hi
I wish to save a macro enabled template file to a macro enabled file in D:\Invoice\2012\ with a file name equivalent to the value of cell k3 in sheet 1.
I tried the following macro but ended up with a error

Please Login or Register  to view this content.

It works only till the "save as" screen and once I press save, it shows error "Runtime error '1004'. This extension cannot be used with selected file type and so on..
Originally I got this macro from http://www.excelforum.com/excel-prog...-template.html, customized it a little based on my requirement but still ended up with an error.

A little help would be much appreciated.

Thanks
Anand

**snb** · 01-27-2012, 06:35 AM

Please read the VBEditor's helpfiles on SaveAs, especially the arguments.

Do not copy VBA, nor use it 'blindly'.

**andcha** · 01-27-2012, 07:24 AM

If I had that much time and understanding of excel and vba I wouldn't have asked for help.
And I am not using it blindly, I edited what I thought was logical. Ultimately, what harm can the above macro do to my PC?

Could you please care to elaborate "Do not Copy VBA"

Anand

**royUK** · 01-27-2012, 07:32 AM

What snb means is that we are here to help people learn to use Excel, not do the work for them.

"If I had that much time and understanding of excel and vba I wouldn't have asked for help.

- there are excel consultants available

**andcha** · 01-27-2012, 10:06 AM

Originally Posted by royUK

What snb means is that we are here to help people learn to use Excel, not do the work for them.

I am sorry if my original post portrays that I am here to get people work for me.

I had problem completing certain task, searched about that problem, landed over to http://www.excelforum.com/excel-prog...-template.html, tried to make it work for me, it didn't and after your suggestion I started a new thread, that's it
Isn't that's what forums are for... to help and get help. Even I am a member of lot of forums where I help people if the their posts falls under my area of expertise, I don't ask them to read books.

Anyways, If you still think I am asking for something unjustified, you are most welcome to delete the thread.

**royUK** · 01-27-2012, 10:45 AM

Your code uses Format, without specifying a format, probably it's a number so "000"

Please Login or Register  to view this content.

I can't see the point saving with the macros if it is only saving a copy invoice

**andcha** · 01-27-2012, 12:39 PM

Hi Roy
I greatly appreciate and thank you for all your efforts. The code given by you is simple yet very straight forward and works like a charm.
Meanwhile, I figured out the problem and got the previous code working too, by replacing "xlOpenXMLWorkbook" in the 8th line with "xlOpenXMLWorkbookMacroEnabled"..

Please Login or Register  to view this content.

As for saving with the macro, the template file contains another macro which enables few extra functions which are required to display certain fields and do some calculations, so when I save it as a normal file, those functions doesn't work.

Anyways, Thank you so much.

Anand

**royUK** · 01-27-2012, 01:09 PM

presumably it's an invoice. Why would a completed invoice need changing?

**andcha** · 01-27-2012, 02:01 PM

You're right, it won't need any changing, but it will help me avoid copy pasting a lot of data if I am billing to another company with same products. Will run the macro again from that sheet and new invoice will be created.

**dartagnan** · 06-24-2013, 11:46 AM

I was looking for the same answer as the original questioner, and I was shocked by the snide, unhelpful answers. You must feel compelled to demonstrate your superiority. I mean, if somebody has an honest request for help (such as, in this case: the user had tried to use code found on the web, but was getting an answer he/she didn't understand), then either try to help him out, or don't answer. Your aggressive, repulsive tone didn't help in any way, so why waste your time? You may reign supreme in your role as an Excel guru, but try remembering that other people are actual human beings, and, being human, expect a certain built-in level of grace and courtesy from other human beings.