Use the VBA SaveAs Method in Excel 2007

re: Use the VBA SaveAs Method in Excel 2007

Andy — Wed, 08 Jul 2009 18:29:53 GMT

This works great! Thanks!

Except the worksheet that I am copying & saving has form controls on it, but I don't want them to be copied. How do I change the code to accommodate this?

Thanks,

Andy

andyngretch@Msn.com

re: Use the VBA SaveAs Method in Excel 2007

Ron de Bruin — Wed, 08 Jul 2009 18:48:24 GMT

Hi Andy

See this page for code that you can add to the macro. If you need more help let me know.

http://www.rondebruin.nl/controlsobjectsworksheet.htm

re: Use the VBA SaveAs Method in Excel 2007

Harlan Grove — Fri, 10 Jul 2009 22:02:00 GMT

Seems moderation kicked out my previous comments. I'll try again without code.

Your macro is gross overkill and could be tightened up considerably. However, there's a better way to handle the FileFormat parameter of the .SaveAs method call: use a private function. Pass that function a reference to the original workbook, and encapsulate the code to handle Excel 12+ file formats there rather than in the body of your macro.

Encapsulating picky details, especially those details necessary for handling differences between versions, is usually considered programming best practice. Dumping such details in ad hoc macros isn't programming best practice.

re: Use the VBA SaveAs Method in Excel 2007

Ron de Bruin — Fri, 10 Jul 2009 22:32:59 GMT

Hi Harlan

I am sure there are better ways and please post them.

The problem with seperate functions is that readers from my site always forget to copy them in the module together with the macro.

So I like to add it to the macro so it is a copy/Paste for a user.

You will not now how many private many mails I get from people that forgot to copy the functions that i use on a few of my pages.

And remember Harlan I am a flower grower and no programmer.

Thanks for your reply

re: Use the VBA SaveAs Method in Excel 2007

Weldon Dodd — Sat, 11 Jul 2009 22:36:31 GMT

The new file doesn't have any of the macro modules from the original workbook. Is there a way to pass modules to the new workbook? Ideally, I'd like to pass one module and not all of them.

Thanks!

re: Use the VBA SaveAs Method in Excel 2007

Weldon Dodd — Sat, 11 Jul 2009 22:43:09 GMT

on re-reading I realized that my question is almost the opposite of Andy's. I am using a second worksheet (order history) to prepopulate the first worksheet (order sheet). I want the macros and buttons associated with the order sheet to continue to work in the new workbook that I save for each customer. I do not want the additional worksheets and the file generation macros from the original worksheet (which are all in a separate module from the user controls)

re: Use the VBA SaveAs Method in Excel 2007

Ron de Bruin — Sat, 11 Jul 2009 23:31:21 GMT

Hi Weldon

Use a button of the control toolbox and add your code in the click event of this button.

This event is in the sheet module so your button will work

If you want to mail each sheet see also

http://www.rondebruin.nl/sendmail.htm

re: Use the VBA SaveAs Method in Excel 2007

Rick Henderson — Thu, 16 Jul 2009 17:14:23 GMT

Interesting post Ron. Finally! I've found a VBA blog associated with Microsoft!

re: Use the VBA SaveAs Method in Excel 2007

Jayson — Mon, 20 Jul 2009 23:44:45 GMT

One thing I've learned (the hard way of course) is that there are different FileFormatNum for an .xls created in xl2003 and xl2007. If you are running xl2003 you should use -4143. If you are running xl2007 then use 56.

I've used this bit of code before to take care of this.

If Val(Application.Version) < 12 Then

'You use Excel 97-2003

FileExtStr = ".xls": FileFormatNum = -4143

Else

'you use excel 2007 or later

FileExtStr = ".xls": FileFormatNum = 56

End If

re: Use the VBA SaveAs Method in Excel 2007

Gary B — Thu, 23 Jul 2009 09:19:01 GMT

I liked your article, however, if the sheet that is being copied to a new file contains a chart, then "sometimes" the chart does not appear correctly (as if it has lost its data series) in the new file.

I have found it useful to save the file, with a new name (and a different fileformat), deleting any sheets taht are not required in the final workbook, and removing all vba as the original file has an "On Open" routine that is run, which i don't want run from the final file.

In other words, sometimes it is better to NOT copy a sheet to a new file, but rather copy the file and make changes to the new file. Sometimes :-)

re: Use the VBA SaveAs Method in Excel 2007

Ron de Bruin — Thu, 23 Jul 2009 17:58:55 GMT

Hi Gary

Thanks for you reply

I have this option also in my mail add-ins

http://www.rondebruin.nl/mail/add-in.htm