macro to save file as

[Erik]

I've created a few macros in a workbook. The macros import and then format
the sheet and data the way I need it to be and they all work great.
What I would like is a macro that will prompt the user to save the workbook
as another file name (prompt for location and file name). Once the new
workbook is saved, how do I close the original file without actaully saving
the data in it. (Basically, the file I open is like a template that should
never be changed)

Secondary question....is there a way, when saving as a new file name, to
remove the background macros in the new file or disable them so that another
user of the file does not get the "enable/diable macros" prompt when opening
the new file?

Thanks for the help.

 

[Ronald R. Dodge, Jr.]

First, you may want to look into using "SaveCopyAs" which does have 2
distinct differences from "SaveAs" method. First, it won't prompt the user
is it sure it wants to overwrite the file or not if that exact file name
already exists, and secondly, for anything other workbook currently open
relying on the current workbook, formulas won't be adjusted to the new
location as the current workbook will be still referring to the old location
even though a copy of it has been saved to an entirely different new
location.

For the other part of your question, you will need to have trusted on your
system to access Visual Basic by the following route:

Tools>Options>Security>Macro Security...>Trusted Sources>Trust access to
Visual Basic Project

Once you do that, you then could have that file opened (if you don't already
have another file with that same file name already open within that
particular instance of Excel), then use VBE codes to remove those modules,
or to wipe clean the modules that can not be removed (I.e. Thisworkbook or
the various worksheet modules).

However, given this trust bit, you do want to use extra caution on your own
system as other things can happen if the proper precautions are not used.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Dave Peterson]

How about an alternate approach.

Instead of importing the data into the workbook that contains the code that does
all the work, how about just putting the data into a new workbook.

When I had to do this stuff, I'd create a dedicated workbook that would contain
2 worksheets.

The first worksheet contained instructions -- how to create the data file
(usually a non-excel description) and how to run the macro.

The second worksheet contained a giant button from the Forms toolbar (and maybe
some input choices for the user). The macro that did the work was assigned to
the button.

The data workbook was either created from code like:

Dim RptWks as worksheet
'single sheet workbook
set rptwks = workbooks.add(1).worksheets(1)

Then the data was plopped into that rptwks worksheet.

Later, I would save it using a standard naming convention:

application.displayalerts = false
with rptwks.parent
.saveas filename:="C:\somefolder\SomePrefix" & format(now,"yyyymmdd_hhmmss")
_
& ".xls", fileformat:=xlworkbooknormal
.close savechanges:=false 'close it?
end with
application.displayalerts = true

'and maybe...

Thisworkbook.close savechanges:=false 'close the workbook with the code???

=========
If I had to have macros available (event or other) in that report workbook, then
I'd set up a template workbook that contained all the code I needed. And any
worksheet that needed commandbuttons or events would be added, too. (This is a
manual effort.)

Then I'd use this create the new workbook based on that template file:

set rptwks _
= workbooks.add(Template:="C:\yourpath\myfile.xlt").worksheets("RptSheet")

 

[Erik]

Ok, lets see if I have this straight. I could use a workbook, create all the
macros in it, that would open a new workbook (template) import all the data
that I need, and then could be saved...independent of the workbook with the
macros attached? I am very new to writing code and have learned the little
that I know mostly from trial and error and of course this forum. Can you
give me an example of how to do this? Maybe using names like MacroFile for
the workbook with the macros, TemplateFile for the template one, and
NewDataFile for the finished product? Would that be possible? Because I am
not really sure how to do what you are explaining.


Erik

 

[Dave Peterson]

Yep. That's what I'm saying.

You'll create a new file that contains the instructions and the code and a big
button. That's the place that the importing/reformatting macro will be located.

If you need a template file--and you haven't said why you'd need one--you'd
create a separate workbook that would contain all the worksheets (with
headers/footers/page setup/event macros) and any macro that the file needs after
it's created. These macros would not be associated with the importing code.

And then the macroworkbook would either create an output workbook from scratch
or from that template workbook and import the data into that output workbook.

I don't know what kind of data you're importing or from where, but ...

Open the macroworkbook
click the button
the button opens the text file (is it a text file) and parses the data into
columns.
If you don't use a template, just format the sheet, change the page setup, add
headers, add filters, ..., and save this file.

If you use a template, you can create a new workbook based on that template.
Open the text file.
Copy the data from that text file into its home in the new workbook based on
that template.
close the text file without saving
and continue the work on the data that's now in the new workbook based on that
template.