Disable Macros while 'save-as'?

bjoydeep

New Member
Joined
Nov 7, 2002
Messages
5
Hi!

I am a newbie to VBA macros. So dont know if its a silly question to ask!
I am open an excel workbook, read data from a csv into it (by a Auto_Open) then do some formatting and then saving it as another xl sheet (original xl sheet is untouched) through a Auto_Close macro.
Now while saving the worksheet-as, I would want it to saved without any of the macros.
Is it possible at all?

This is code for autoclose:
Public Sub Auto_Close()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"c:test_jb.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
' Closes workbook
'Application.Quit
ActiveWorkbook.Close
End Sub

Would appreciate if you could help me in anyway.

Thanks
Joydeep
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy JoyDeep, welcome to the board.

Have a look at Chip's procedure, Deleting All VBA Code In A Project, for deleting all code in a workbook, you can tack it on at the end of your auto_close, before end sub

http://www.cpearson.com/excel/vbe.htm

For this to work properly you'll need to reference MS Visual Basic for Applications Extensibility library in your visual basic references. The following code should set it up for as well:<pre>
Sub WrkWRefs()
Dim n As Integer, p As String
For n = 1 To ThisWorkbook.VBProject.References.Count
p = ActiveWorkbook.VBProject.References.Item(n).Description
If InStr(p, "Visual Basic for Applications Extensibility") Then _
GoTo 1
Next n
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 1, 0
1: End Sub</pre><pre></pre>
Edit: Be careful with this procedure, test on a copied version of the real deal, until you have a handle on the order of operations... It will delete your code.<pre></pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-08 19:51
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This code will save the current sheet as a new workbook with no macros, all the macros stay in the original workbook. Hope this helps. JSW

Sub mySaveSheet()
Dim mySheet
Dim myFile
'Run this from the current Workbooks sheet
'module for the sheet you want to save.
myFile = ActiveWorkbook.Name
'The current Work-Sheet.
mySheet = ActiveSheet.Name
Sheets(mySheet).Select
'Make a copy of the current Work-Sheet.
Sheets(mySheet).Copy
'Save this Work-Sheet using the Excel "Save-As" dialog screen.
Application.Dialogs(xlDialogSaveAs).Show
'Keep the new file open!
Application.Workbooks(myFile).Activate

End Sub
 

bjoydeep

New Member
Joined
Nov 7, 2002
Messages
5
Hi Nate,

Thanks for the prompt response.

I understand adding the 'Chips Procedure' part but where do I add Sub WrkWRefs(). BTW, I am running Excel 2000.

Once again apologies for being dumb.

Appreciate your help!

Joydeep
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

Howdy JoyDeep,

Actually Joe make's a good point, if it's a one sheet workbook with no code in the worksheet code module, Chip's procedure may be a little gratuitous...

But if you put WrkWRefs() in any normal module in any workbook, it should establish the reference for your application for all workbooks once launched. It was written and tested in xl 2000 & win 2k.

Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-08 20:13
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Consider taking the time to figure out how to write an add-in. In addition to helping keep your code separate from your data, it will open a realm of possibilities in good programming methodology. IMO, as far as XL programming goes, learning about add-ins will be one of the best investments you make.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

In addition to helping keep your code separate from your data, it will open a realm of possibilities in good programming methodology.

Well that's a good point, nice add-on (if you will)! Another thought along these lines, would be to keep your procedure/macro in the personal.xls file or another hidden workbook which could be in xlstart folder (just for the sake of further separation). Since you're using activeworkbook your code should function fine on the active window.

I tend to [frequently] create code-only workbooks to work with active/target files...

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-10 22:22
 

bjoydeep

New Member
Joined
Nov 7, 2002
Messages
5
Hi Guys

This is great! I could get Joe's script to work. Another question:
-how to do the 'save as a file name' programmatically without the dialog box to pop-up
-would the same thing work if we have to worksheets in the workbook.

BTW, Nato, dont why, but the DeleteAllVBA doesnt seem to work (it compiled fine).

Thanks for all the help!

Joydeep
 

bjoydeep

New Member
Joined
Nov 7, 2002
Messages
5
One of the quetsions was dumb.

I can definitely save the worksheet as a file w/o the dialog box popping up. Thats done and working.
Here is the code:
Public Sub Auto_Close()
Application.DisplayAlerts = False
'--ActiveWorkbook.SaveAs Filename:= _
"c:test_jb.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
' Closes workbook
'Application.Quit
'---ActiveWorkbook.Close
'--Call DeleteAllVBA
Dim mySheet
Dim myFile
'Run this from the current Workbooks sheet
'module for the sheet you want to save.
myFile = ActiveWorkbook.Name
'The current Work-Sheet.
mySheet = ActiveSheet.Name
Sheets(mySheet).Select
'Make a copy of the current Work-Sheet.
Sheets(mySheet).Copy
'Save this Work-Sheet using the Excel "Save-As" dialog screen.
'--Application.Dialogs(xlDialogSaveAs).Show
Sheets(mySheet).SaveAs Filename:= _
"c:test_jb.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'Keep the new file open!
Application.Workbooks(myFile).Activate
End Sub

But how will I extend this when I have 2 worksheets in the workbook. And why is DeleteAllVBA not working?

Thanks again.
Joydeep
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy JoyDeep, seems to me the procedure works fine. But to copy two worksheets, and to streamline a little, try the following:

<pre>
Public Sub Auto_Close()
'1st & 3rd Sheet
Sheets(Array(1, 3)).Copy
ActiveWorkbook.SaveAs "c:test_jb.xls"
End Sub</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top