|
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 --- Regards, Norman "nbaj2k" wrote in message ... 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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "nbaj2k" wrote in message ... 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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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").Co deModule 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 wrote: 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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
Użytkownik "nbaj2k" 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 Any help would be great!, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
nbaj2k wrote: 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 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 '<<============= --- Regards, Norman "nbaj2k" wrote in message ... 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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 '<<============= --- Regards, Norman "nbaj2k" wrote in message ... 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 ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
Hi Sandy
Have you try my sendmail add-in There is a option to delete the code -- Regards Ron de Bruin http://www.rondebruin.nl "SandyUK" wrote in message ... 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 -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
Hi Sandy,
Where did you find the RemoveAllMacros code? --- Regards, Norman "SandyUK" wrote in message ... 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 -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "SandyUK" wrote in message ... 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 -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
Remove Macros before send? & Runtime error if choosing not to overwrite file?
Thanks again Ron All the best Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=569162 |
| All times are GMT +1. The time now is 08:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com