Remove Macros before send? & Runtime error if choosing not to overwrite file?

[nbaj2k]

I have my macros all done up doing exactly what I want. I have run into
2 problems however.

When I have it set to save a file, if the filename already exists, it
asks if I wish to overwrite, if I say yes, it continues on fine, if I
say no, the macro stops and crashes, saying

"Run-time error '1004'
Method 'SaveAs' of object '_Workbook' failed

Is there a way if I say no for it to continue on instead of crashing?

The other problem I have is when I send out reports that I used macros
to create. I just want people to be able to view the reports of the
data, but instead a box comes up when they try to open them saying that
the file contains macros, and that "Macros may contain viruses, it is
usually safe to disable macros". Is there anyway of automatically
removing any macros from a file before it sends?

Any help would be great!,

~J

 

[Norman Jones]

Hi N,

(1) Post the problematic code

(2) See Chip Pearson's page on programming to the VBE at:


See particularly the section entitled:

Deleting All VBA Code In A Project

 

[Ron de Bruin]

Hi nbaj2k

You can use

Application.DisplayAlerts = False
'code
Application.DisplayAlerts = True


You can use my mail add-in to send without macro's or use code from Chip's site
http://www.rondebruin.nl/mail/add-in.htm

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

 

[Joe HM]

Hello -

You might want to do something like ...

On Error GoTo Skip
ThisWorkbook.SaveAs Filename:=lFN, FileFormat:=xlNormal
....
Skip:

.... or ...

On Error Resume Next
ThisWorkbook.SaveAs ...

.... in order to avoid the crash.

Deleting Macro code can be done as follows ...

Dim VBCodeModule As CodeModule
Set VBCodeModule =
ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule

With VBCodeModule
.DeleteLines 1, .CountOfLines
End With

.... where you will need a reference to Microsoft Visual Basic for
Applications Extensibility 5.3 (or similar) under Tools > References.

Hope this helps,
Joe

 

[nbaj2k]

Right now for the saving I have it like this

Dim filename As String

filename = Range("A1")
ChDir "G:\Reports\First\"
ActiveWorkbook.SaveAs filename:= _
"G:\Reports\First\" & filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No
_
, CreateBackup:=False

If the file is already created and I choose not to overwrite, it
crashes.

I will try that code for the other part when I get back to work
tomorrow, thanks.

~J

 

[Gazeta]

U¿ytkownik "nbaj2k" <[email protected]>
napisa³ w wiadomoœci

I have my macros all done up doing exactly what I want. I have run into
2 problems however.

When I have it set to save a file, if the filename already exists, it
asks if I wish to overwrite, if I say yes, it continues on fine, if I
say no, the macro stops and crashes, saying

"Run-time error '1004'
Method 'SaveAs' of object '_Workbook' failed

if u want to avoid those prompts u can use
Application.DisplayAlerts = False at the begginning of the code
and Application.DisplayAlerts = true at the end - file will be saved even if
the same file already exists in the given location

if y want to avoid overwriting of existing file u have to add code that will
save it under second given name

Is there a way if I say no for it to continue on instead of crashing?

The other problem I have is when I send out reports that I used macros
to create. I just want people to be able to view the reports of the
data, but instead a box comes up when they try to open them saying that
the file contains macros, and that "Macros may contain viruses, it is
usually safe to disable macros". Is there anyway of automatically
removing any macros from a file before it sends?

easiest way would to to copy your data to temporay files and send those
temporary files
that should help if your macroc are in modules
if u want to delete macros before sending ( include code that will delete
your code inside your file u can go to http://www.cpearson.com/excel/vbe.htm

hth
mcg

 

[ducky]

I have my macros all done up doing exactly what I want. I have run into
2 problems however.

When I have it set to save a file, if the filename already exists, it
asks if I wish to overwrite, if I say yes, it continues on fine, if I
say no, the macro stops and crashes, saying

use application.screenupdating = false (be sure to turn it back on at
the end)

The other problem I have is when I send out reports that I used macros
to create. I just want people to be able to view the reports of the
data, but instead a box comes up when they try to open them saying that
the file contains macros, and that "Macros may contain viruses, it is
usually safe to disable macros". Is there anyway of automatically
removing any macros from a file before it sends?

there are two options:
1 - write complex code ( i think some people already posted suggestions
for you)
2- keep your code in personal.xls and let it run on your 'target' WB.
after you save it, the code will not have carried over.

AR

 

[Norman Jones]

Hi N,

Perhaps try something like:

'=============>>
Public Sub Tester()
Dim FName As String
Const myPath As String = "G:\Reports\First\"
Dim res As VbMsgBoxResult

FName = Range("A1")

If Dir(myPath & FName) <> "" Then
res = MsgBox(Prompt:="The file already exists. " _
& "Do you wish to overwrite it?", _
Buttons:=vbYesNo)
End If

If res = vbYes Then
ChDir myPath
ActiveWorkbook.SaveAs filename:=myPath & FName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=No, _
CreateBackup:=False
Else
'do nothing?
End If
End Sub
'<<=============

 

[nbaj2k]

The code almost works. It asks me whether I want to overwrite or not,
If I say no it goes by it, if someone says yes however, it then comes
up saying the file already exists whether to overwrite or not, if
someone picks no to that it still crashes.

I personally wouldn't care but there are other people that might need
to use this so I was wondering if there was a way around that or not.

From the Pearson site that was given I did find this code for deleting
modules, I was wondering if there is a way to select all modules or not
or if they have to be listed individually.

Thanks,

~J

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

 

[nbaj2k]

Anyone have anything else that might help, I'm still unable to remove
macros from spreadsheets. When I send out reports everyone is
receiving reports that have macros attached so they get messages
popping up, anyone have anything that could help?

~J

 

[nbaj2k]

nevermind I got it. I found this code that I was able to use. The site
I got it from is listed below.

~J

http://tinyurl.com/k4cm3

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub

 

[SandyUK]

I found this thread on the site and the RemoveAllMacros sub is exactly
what i want but i am having trouble making it work.

I have copied it into ThisWorbook in a test workbook but it doesn't
appear in the macro list when i come to run it and really what i need
to do is have it in my personal.xls and then have it run against the
ActiveWorkbook

I think it something simple (gap in my limited knowledge) so any help
appreciated.

Regards

Adrian

 

[Norman Jones]

Hi N,

Try:

'=============>>
Public Sub Tester()
Dim FName As String
Const myPath As String = "G:\Reports\First\"
Dim res As VbMsgBoxResult

FName = Range("A1")

If Dir(myPath & FName) <> "" Then
res = MsgBox(Prompt:="The file already exists. " _
& "Do you wish to overwrite it?", _
Buttons:=vbYesNo)
End If

If res = vbYes Then
Application.DisplayAlerts = False
ChDir myPath
ActiveWorkbook.SaveAs Filename:=myPath & FName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=No, _
CreateBackup:=False
Application.DisplayAlerts = True
Else
'do nothing?
End If
End Sub
'<<=============

 

[Ron de Bruin]

Hi Sandy

Have you try my sendmail add-in
There is a option to delete the code

 

[Norman Jones]

Hi Sandy,

Where did you find the RemoveAllMacros code?

 

[SandyUK]

Hi Norman

I found the code at
http://www.exceltip.com/st/Delete_al...Excel/505.html

Hi Ron

I did have a look at the explination of your addin which looks really
good and will achieve what i am after and some other cool stuff which i
am sure will come in useful. A big thanks for posting and for your site,
it along with chips has helped me develope my skills to the limited
level they are (but getting better all the time )

The reason i have not used it (yet) is I am trying to understand how to
code VBA so that i can develope more of my own stuff and hopefully get
my skills to a level that I can give back to the forums.

I am 99% on the what the RemoveAllMacros sub is doing but can't
understand why i am not able to "see it" in the VBA or how to run it
against the active workbook or a specified workbook and thats going to
drive me mad until i find out how.

Adrian

 

[Ron de Bruin]

Look at the macro on Chip's site
See "Deleting All VBA Code In A Project "
http://www.cpearson.com/excel/vbe.htm

Note he use ActiveWorkbook
You can replace that with a workbook name if you want

 

[SandyUK]

Thanks again Ron

All the best

Adrian