Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello all
well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific email address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
whether changing the file name extension would work would depend on the
software that is intercepting the file. You other ideas sound like they would work. A lot would depend on your implementation. http://www.cpearson.com/excel/vbe.htm has code examples for removing code in a copy of the workbook. -- Regards, Tom Ogilvy "DawnTreader" wrote: Hello all well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific email address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
also, if you don't have any code in the worksheet modules, you could do
activsheet.copy ' or worksheets(array(1,2,3)).Copy Activeworkbook.Sendmail . . . activeworkbook.close SaveChanges:=False -- Regards, Tom Ogilvy "DawnTreader" wrote: Hello all well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific email address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Lots of possible solutions. Here is one possibility:
Consider: - Select entire data entry worksheet/UsedRange).Copy - create a new workbook - paste copied values into new book - paste copied formats over data you just pasted - Save & email new book - ? remove entries from data entry book & resave - ? or simply close data entry book without saving (i.e., in its original state) This would give you your formatted data (no formulas or code) in a clean workbook. The following code is a bare-bones *functioning* version of the 1st 4 steps above (assumes sheet with data is Active when called). HTH Sub CopyValuesAndFormatsOfSheetToNewWorkbook() ActiveSheet.Cells.Copy Workbooks.Add 'Note: NewWorkbook becomes the ActiveWorkbook automatically 'So ActiveSheet now points to New workbook as well. 'If you need/want to set references, now is the time to set them: '(Optional) Set wkbNew=ActiveWorkbook ActiveSheet.Cells.PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False ActiveSheet.Cells.PasteSpecial _ Paste:=xlPasteFormats, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False End Sub "DawnTreader" wrote in message ... Hello all well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello
well, changing the extention doesnt work. i still got the same result from my internet provider, and hotmail damaged the file so that the VBA was stripped from it. i really dont want to take the code out, but it looks like that might be my only easy option. the other is working out code to put it in a RAR file. i know that there is a ZIP VBA routine out there, but i wonder what it would take to adapt it to RAR. why? because ZIPs still get stopped. "DawnTreader" wrote: Hello all well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific email address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
This is a wild shot. But apt.
If your running Outlook and can access Outlook from home ... Try storing the XL document in your Drafts folder - and NOT emailing it. When you're at home fetch it from your Drafts folder. -- If you don't have Outlook. Create an account at geocities.yahoo.com. It looks like a pay service, but actually, if you look hard enough you'll discover you can save more than a GiG for free. Save your XL document there and fetch it via http. --- Or create a yahoo email account. Send a message to yourself and attach the XL worksheet. See if that works. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello
okay, maybe i missed one fact from the first post. my technicians need to be able to do this from thier outlook enabled laptop from any internet connection in the world. this causes the situation where it has to work from an outside email, outside of our companies intranet, and be able to be attached, carried, and eventually opened from the inside. creating a yahoo account, or saving it in the drafts folder doesnt help. this has to be able to traverse all the security, and be dummy proof. thanks for the suggestion though. :) " wrote: This is a wild shot. But apt. If your running Outlook and can access Outlook from home ... Try storing the XL document in your Drafts folder - and NOT emailing it. When you're at home fetch it from your Drafts folder. -- If you don't have Outlook. Create an account at geocities.yahoo.com. It looks like a pay service, but actually, if you look hard enough you'll discover you can save more than a GiG for free. Save your XL document there and fetch it via http. --- Or create a yahoo email account. Send a message to yourself and attach the XL worksheet. See if that works. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
I still don't know exactly what it is that you want.
It sounds to me like you're a virus writer and you're trying to obfuscate mailicous VB code. But here is another idea ... Try sending some VBscript as a text file and see if that gets through the filter. If it gets through works you can rewrite your Excel workbook as a VBScript. Have the VBScript create the Excel Workbook. Also, try sending an html file with VBScript file to yourself as a HTA file and put VBScript in it an see if that gets through. If this works then you'll be able to send an executable VBScript that will build the Excel document when it gets opened. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Opps I forget to mention. If you build the Excel workbook from VBScript you can keep the VB Modules separate from the VBS that creates the Workbook. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello
Could i just activesheet copy to a new workbook and then send the new workbook? there are lots of formulas and fancy stuff in the sheet not sure if it would break if i copy paste sections of it. do named ranges on a sheet get copied with thier names intact? got to do some testing i guess. "George Nicholson" wrote: Lots of possible solutions. Here is one possibility: Consider: - Select entire data entry worksheet/UsedRange).Copy - create a new workbook - paste copied values into new book - paste copied formats over data you just pasted - Save & email new book - ? remove entries from data entry book & resave - ? or simply close data entry book without saving (i.e., in its original state) This would give you your formatted data (no formulas or code) in a clean workbook. The following code is a bare-bones *functioning* version of the 1st 4 steps above (assumes sheet with data is Active when called). HTH Sub CopyValuesAndFormatsOfSheetToNewWorkbook() ActiveSheet.Cells.Copy Workbooks.Add 'Note: NewWorkbook becomes the ActiveWorkbook automatically 'So ActiveSheet now points to New workbook as well. 'If you need/want to set references, now is the time to set them: '(Optional) Set wkbNew=ActiveWorkbook ActiveSheet.Cells.PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False ActiveSheet.Cells.PasteSpecial _ Paste:=xlPasteFormats, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False End Sub "DawnTreader" wrote in message ... Hello all well, what started out as a little project has turned into a major one. i work in a department of a company where we have technicians who need to send in a time card that records hours that they are doing service outside of the manufacturing and production shop that they work in when not doing service. up to this time we have had at least one problem every week that time cards are submitted with either the actual information in the time card, or who it is submitted to or the file name not conforming to the way we want it submitted. so i have been working on automating the submission of time cards. i have everything working exactly the way i want it. when the workbook opens all the toolbars are removed, a dialog pops up and asks for thier name, employee number, and the date of the sunday at the beginning of the week they are submitting for. i even used combo boxes for the date entry. then i have a button which saves the file into a specific folder in the users my documents, that folder is created if it isnt there, then it creates an email with the workbook as an attachment and emails it to a specific address, adds the toolbars back, and finally closes excel and the workbook. it is a pretty piece of automation if i do say so myself. :) except that when i tried to send it from home to myself at work, to test whether it will work from outside the office, it failed. "Your message was rejected by (our server) for the following reason: MS-Office file containing VBA macros found inside of the email The following recipients did not receive this message: We hope this information is helpful." ARRGH! so my first thought is to save the workbook, then save it again stripped of all VBA as a separate file, then to email the stripped file, then to delete the stripped file and then finish as before. is that the best solution? i was also thinking of having the code not put an extention on the file, email it and then i can correct the extention when i recieve it. is that going to work? another thought is, would the system i have set up work as an add in that causes a workbook to be sent that doesnt have VBA in it? as i have worked on this, i have written it in other places. i need this to be as dummy proof as possible. any suggestions or ideas or code posted would be greatly appreciated. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello
" wrote: It sounds to me like you're a virus writer and you're trying to obfuscate mailicous VB code. no i am not. i guess this just wont work. i am really tired of microsoft screwing up the abilities of thier programs. it is amazing to me how complicated and how many roadblocks i found trying to simply email a spreadsheet. i thought i had found a way to make things easy for my technicians to input and send me a copy of thier timesheet. but now it seems that i need to write my own program outside of excell to make this work. what is microsoft thinking? i understand the need for security, but there has to be some way to make this work. thanks for the suggestion about the vb script. but that wont work. i have spent more time than i should have on this project. i need an elegant solution to this problem, something so simple that it wont take much more vba programming, and will get around the filters in any email system. any ideas or suggestions would be appreciated. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hi Dawn,
I just noticed this thread and became curious. I am curious why the time sheet e-mailed to you would need any code in it at all. Wouldn't it be easy to structure your code around sending and receiving a time sheet with no VBA included? On the user computer, the install the code necessary to do what ever you want to do on their end to compile/send a simple time sheet. On your computer, install the code necessary to do what ever you need it to do to extract the information from a simple time sheet. I am a firm believer in keeping all code out of a worbook that may be passed on to other users. (I'm speaking with my company mentality goin' on.) Multiple copies multiply to the nth degree causing storage costs to rise. I see no reason for the need to send a workbook with VBA in it to pass any data. I know, if there is data in a cell, I can extract it, so why do I need code in the workbook? If you do not have access to all the user computers, and need them to have a module installed, send them a notepad file of your modules, with installation instructions, & talk them through the install over the phone. I have done this hundreds of times and it doesn't takes more than 10 minutes with the dimmest lights. This is 100% guaranteed to pass any e-mail server worldwide. Regards, Alan "DawnTreader" wrote in message ... Hello " wrote: It sounds to me like you're a virus writer and you're trying to obfuscate mailicous VB code. no i am not. i guess this just wont work. i am really tired of microsoft screwing up the abilities of thier programs. it is amazing to me how complicated and how many roadblocks i found trying to simply email a spreadsheet. i thought i had found a way to make things easy for my technicians to input and send me a copy of thier timesheet. but now it seems that i need to write my own program outside of excell to make this work. what is microsoft thinking? i understand the need for security, but there has to be some way to make this work. thanks for the suggestion about the vb script. but that wont work. i have spent more time than i should have on this project. i need an elegant solution to this problem, something so simple that it wont take much more vba programming, and will get around the filters in any email system. any ideas or suggestions would be appreciated. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing VBA from a workbook
Hello Alan
actually i dont want to send a time sheet with code in it. i want to send a spreadsheet through my companies email server. the problem is that our firewall was not set up properly. at this time it is working beautifully. i have a template that the users will have, they will open the workbook and then it will help them in entering the required information, and when they press the button i have on the sheet it will email a copy of itself to the appropriate email and save itself to the appropriate directory on thier laptop. i had been explaining what i was trying to do to our IT staff and the other day the boss was complaining about the fact that he had to use an AOL account to get email from certain customers. when the boss is working around the companies own mail server you know there is a problem. the IT staff looked at the situation and everything has been resolved, including my spreadsheet problem. WOO HOO! so thanks for the suggestion, but at this time i am happy with what i have. thanks to all who helped me with this, in this and other threads. :) "Alan" wrote: Hi Dawn, I just noticed this thread and became curious. I am curious why the time sheet e-mailed to you would need any code in it at all. Wouldn't it be easy to structure your code around sending and receiving a time sheet with no VBA included? On the user computer, the install the code necessary to do what ever you want to do on their end to compile/send a simple time sheet. On your computer, install the code necessary to do what ever you need it to do to extract the information from a simple time sheet. I am a firm believer in keeping all code out of a worbook that may be passed on to other users. (I'm speaking with my company mentality goin' on.) Multiple copies multiply to the nth degree causing storage costs to rise. I see no reason for the need to send a workbook with VBA in it to pass any data. I know, if there is data in a cell, I can extract it, so why do I need code in the workbook? If you do not have access to all the user computers, and need them to have a module installed, send them a notepad file of your modules, with installation instructions, & talk them through the install over the phone. I have done this hundreds of times and it doesn't takes more than 10 minutes with the dimmest lights. This is 100% guaranteed to pass any e-mail server worldwide. Regards, Alan "DawnTreader" wrote in message ... Hello " wrote: It sounds to me like you're a virus writer and you're trying to obfuscate mailicous VB code. no i am not. i guess this just wont work. i am really tired of microsoft screwing up the abilities of thier programs. it is amazing to me how complicated and how many roadblocks i found trying to simply email a spreadsheet. i thought i had found a way to make things easy for my technicians to input and send me a copy of thier timesheet. but now it seems that i need to write my own program outside of excell to make this work. what is microsoft thinking? i understand the need for security, but there has to be some way to make this work. thanks for the suggestion about the vb script. but that wont work. i have spent more time than i should have on this project. i need an elegant solution to this problem, something so simple that it wont take much more vba programming, and will get around the filters in any email system. any ideas or suggestions would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing a macro from a workbook | Excel Worksheet Functions | |||
Removing macros from workbook | Excel Discussion (Misc queries) | |||
Removing link to another workbook | Excel Discussion (Misc queries) | |||
Removing code in 'This Workbook' | Excel Programming | |||
Vba - Removing workbook | Excel Programming |