Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: save the workbook without saving macro

Status
Not open for further replies.

pho01

Programmer
Mar 17, 2003
218
US
I wonder if there is a way to save the workbook without saving the macro.
I have a data source driver set up on my machine, which retrieves the data from the database and the macro is in the open workbook event. the worksheet is posted, therefore, I don't want other users to hit 'enable macro' because the macro will run into error.

It would be nice to save the workbook without saving the macro. Is it possible?
Thanks,

ActiveWorkbook.SaveAs Filename:= _
"path/to/filename.xls", _
FileFormat:=xlNormal, ReadOnlyRecommended:=True, CreateBackup:=False
 
Have you considered creating a new workbook and copy one or more sheets with data? The code below copies first worksheet and sheet named "DataSheet" to a new workbook. Only code existing in copied sheets code modules is copied. Can be used in BeforeSave event handler:

[tt]Dim wbkExp As Workbook
Dim SheetsToCopy As Sheets
Set SheetsToCopy = ThisWorkbook.Worksheets(Array(1, "DataSheet"))
Set wbkExp = Workbooks.Add(xlWBATWorksheet)
SheetsToCopy.Copy After:=wbkExp.Worksheets(1)
Application.DisplayAlerts = False
wbkExp.Worksheets(1).Delete
Application.DisplayAlerts = True
wbkExp.SaveAs Filename:="PathAndFileName"[/tt]

combo
 
Hi pho01,

If you want to delete the macro as a matter of routine, you must have other copies of it, so what exactly is it and how does it come to be in the workbook in the first place and does it need to be there? If it is only run by you could you have it in your personal.xls?

Enjoy,
Tony
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top