Removing VBA from a workbook

[DawnTreader]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[George Nicholson]

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]

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.

 

[gimme_this_gimme_that]

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.

 

[DawnTreader]

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.

 

[gimme_this_gimme_that]

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.

 

[gimme_this_gimme_that]

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.

 

[DawnTreader]

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.

 

[DawnTreader]

Hello

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.

 

[Alan]

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]

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.