Save Macro enabled WB as w/o macro

[Michael Dobony]

I have the following VBA in my macro-enabled workbook template:

Sub SaveAsCompanyName()
Dim MyPath As String
MyPath = ThisWorkbook.Path
ThisWorkbook.SaveAs Filename:=MyPath & "\" & Range("A1") & " LTL RFQ.xlsx",
_
FileFormat:=xlOpenXMLWorkbook
End Sub

This results in the following error message:

The following features cannot be saved in macro free workbooks.
VB Project
To save a file with these features, click no, and then choose a macro -
enabled file type in the file type list.
To continue saving as a macro free workbook, click yes.

I've searched the web, but can't find a way to automatically bypass this
error message. I do not want to send the final workbook out with macros in
it. Is there a way to code a bypass or auto "Yes" response to this to avoid
this?

 

[GS]

I have the following VBA in my macro-enabled workbook template:

Sub SaveAsCompanyName()
Dim MyPath As String
MyPath = ThisWorkbook.Path
ThisWorkbook.SaveAs Filename:=MyPath & "\" & Range("A1") & " LTL
RFQ.xlsx", _
FileFormat:=xlOpenXMLWorkbook
End Sub

This results in the following error message:

The following features cannot be saved in macro free workbooks.
VB Project
To save a file with these features, click no, and then choose a macro
- enabled file type in the file type list.
To continue saving as a macro free workbook, click yes.

I've searched the web, but can't find a way to automatically bypass
this error message. I do not want to send the final workbook out with
macros in it. Is there a way to code a bypass or auto "Yes" response
to this to avoid this?

If you don't want to send the file with macros in it then just delete
the macros!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Optionally...

If you need to reuse the macro you can store it in PERSONAL.XLS so it's
always available when you use Excel. You can modify it to work for any
open file...

Sub SaveAsCompanyName()
Dim sPath$
sPath = "\" & Range("A1") & " LTL RFQ.xlsx"
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & sPath, _
FileFormat:=xlOpenXMLWorkbook
End Sub

...where ActiveWorkbook is the new file created from your template.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Michael Dobony]

Optionally...

If you need to reuse the macro you can store it in PERSONAL.XLS so it's
always available when you use Excel. You can modify it to work for any
open file...

Sub SaveAsCompanyName()
Dim sPath$
sPath = "\" & Range("A1") & " LTL RFQ.xlsx"
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & sPath, _
FileFormat:=xlOpenXMLWorkbook
End Sub

..where ActiveWorkbook is the new file created from your template.

This is essentially a template file for another user who is not that Excel
savy and very picky, hence the need to bypass this warning when saving.

 

[GS]

This is essentially a template file for another user who is not that
Excel savy and very picky, hence the need to bypass this warning when
saving.

You can't have it both ways in XL2007 and later! If a workbook has VBA
then it MUST be saved as XLSM or XLSA!

Distribute an addin (which is where the code resides) to everyone that
uses this workbook as a template. Create new files from the template
and run 'SaveAsCompanyName' on it.

Optionally, store the addin and template on a network share where
everyone can use it from. Have the addin make its own toolbar so its
functionality is accessible from custom menus.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Michael Dobony]

You can't have it both ways in XL2007 and later! If a workbook has VBA
then it MUST be saved as XLSM or XLSA!

You just don't get it, I want the macros GONE when sent on outside the
company. I have a VERY finicky and demanding coworker I'm adapting this for
and she has too much to do already than to play around with these "little
things." These "little things" add up to a lot of overtime.

Right now it doesn't even matter. I finally had an opportunity to get on
her machine and find the macro that nobody could find and did a temporary
fix on it. Now I have to go in and change absolute cell references to range
names and then changes in the raw data format will not affect the macro. It
took me a full day to read and follow through on the thirteen pages of text
and fixing one subroutine at a time to adapt it.

Distribute an addin (which is where the code resides) to everyone that
uses this workbook as a template. Create new files from the template
and run 'SaveAsCompanyName' on it.

And then have the fun of decoding the addin and redistributing and HOPE you
get all of them.

Optionally, store the addin and template on a network share where
everyone can use it from. Have the addin make its own toolbar so its
functionality is accessible from custom menus.

That's another option, but as a macro enabled workbook, as nobody could
find it in the Personal workbook before and nobody really wants macros
after their experience with these. They're mellowing out now as I fixed
this one after fighting with doing the job by formulas and creating simple
macros to clean up the files afterward, removing links to other workbooks.
The system was a nightmare and I'm trying to make it simple.

 

[CellShocked]

You can't have it both ways in XL2007 and later! If a workbook has VBA
then it MUST be saved as XLSM or XLSA!

What I do is copy the text of the macro, as in all of those you want to
use. Then I place them in another worksheet, in a single cell and name
the worksheet "MacroTxt" or the like.

Then I save it as a NON macro enabled file.

Then I make an instruction worksheet which tells the user how to cut
and paste the text into the macro editor and save the workbook as a macro
file type, and then close and re-open the xlsm file.

 

[Michael Dobony]

What I do is copy the text of the macro, as in all of those you want to
use. Then I place them in another worksheet, in a single cell and name
the worksheet "MacroTxt" or the like.

Then I save it as a NON macro enabled file.

Then I make an instruction worksheet which tells the user how to cut
and paste the text into the macro editor and save the workbook as a macro
file type, and then close and re-open the xlsm file.

I DON"T want the macros saved! I just want to save the worksheet, a copy
from a template directory, with all the formulas and macros removed to send
on to clients after all the information gathering is competed.

 

[Claus Busch]

Hi Michael,

Am Sat, 30 Mar 2013 10:35:04 -0500 schrieb Michael Dobony:

I DON"T want the macros saved! I just want to save the worksheet, a copy
from a template directory, with all the formulas and macros removed to send
on to clients after all the information gathering is competed.

try:

Sub SaveAsCompanyName()
Dim MyPath As String
Dim i As Integer

MyPath = ThisWorkbook.Path

'Deletes the formulas
For i = 1 To Sheets.Count
With Sheets(i).UsedRange
.Value = .Value
End With
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=MyPath & "\" & _
Range("A1") & " LTL RFQ.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
End Sub


Regards
Claus Busch

 

[Zaidy036]

I DON"T want the macros saved! I just want to save the worksheet, a copy
from a template directory, with all the formulas and macros removed to send
on to clients after all the information gathering is competed.

One way to remove formulas is to use the free "ASAP Utilities" from
http://www.asap-utilities.com/

It has a function to change formulas to their calculated values.

 

[GS]

You just don't get it, I want the macros GONE when sent on outside

the
company. I have a VERY finicky and demanding coworker I'm adapting
this for
and she has too much to do already than to play around with these
"little
things." These "little things" add up to a lot of overtime

Actually, I do 'get it'! What I'm trying to convey to you is that the
macros used to process the template do not need to be (nor should be)
stored in the template! You can store them in PERSONAL.XLS or an addin
so they're always available when you need/want to use them.

IOW, a template file is used by a VBA project for processing that
project's intended task. The code resides in the project NOT the
template!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion