HELP: excel macro programming woes.... :S

[KevinGPO]

I have a excel problem.

I am writing a VBA macro for a generic Excel template to:

0. Upon first load up, immediately SaveAs (date-timestamp).xls to make a
copy of itself so that the generic template file can be opened by another
automated application.
1. Parse cell B1 for a path to save to. Parse other cells for data to be
used in final filename.
2. Delete all macro code.
3. SaveAs using final filename to path specified in cell B1.
4. Delete old (date-timestamp).xls file.
5. Must terminate Excel application and must not prompt user to save.

I have found that if the path is a network drive then saving takes 10
minutes. However if the path is a local drive then the save is
instantaneous. Then if I copy the file using Windows Explorer then that's
also instantaneous.

I believe Excel SaveAs function is very very slow when saving across a
network. Maybe it sends it out in very tiny packets. I don't know.

So I want to get around this time problem by writing the macro to Save to
local drive and then copy itself using the FileCopy command. However this
doesn't seem possible for #2 "Delete all macro code" & #4 "Delete old
date-timestamp.xls file" to work now.

Do you have any advice/suggestions on how I can get this to work?

 

[KevinGPO]

Actually, is it possible to save a copy of the current excel file, but
without the VBA macro code? I don't think this is possible but would be most
ideal.

How can I make a copy of the current workbook (all worksheets, etc.). That
way I can strip all VBA macro code from this new clone workbook. Then save
that new workbook as another file. Is this possible?

 

[Tom Ogilvy]

Sheets.copy

should copy all the sheets to a new workbook. This assumes you don't have
code in the sheet modules.