HOw do you save a file using a macro?

  • Thread starter Thread starter kelleygirl
  • Start date Start date
K

kelleygirl

I'm trying to save a file with a macro. I have 90 different files that
I'm running the same macro on. When it's done I want the macro to save
it but with a different name.

Right now the code is setting the name of the file to the first name I
put in (e.g. CMAC301). I want the next file to be named CMAC302. How
do I do that?

When I had Office 97, I was able to somehow pause the macro, input data
as I needed and then resume. Is this still available?

HELP!!!!!!!!!!!!!!!!!!!!!!



------------------------------------------------




------------------------------------------------
 
-----Original Message-----
I'm trying to save a file with a macro. I have 90 different files that
I'm running the same macro on. When it's done I want the macro to save
it but with a different name.

Right now the code is setting the name of the file to the first name I
put in (e.g. CMAC301). I want the next file to be named CMAC302. How
do I do that?

When I had Office 97, I was able to somehow pause the macro, input data
as I needed and then resume. Is this still available?

HELP!!!!!!!!!!!!!!!!!!!!!!
Click to expand...

Not sure if I quite follow what you need to do - see if
this sounds useful..

If the files are created one at a time and you need to
save each one as you go then you need a way to track the
counter - here I've got a Workbook called Central where I
input the "CMAC" text and the start of the count. Each
time the macro is run the counter is advanced and the
central worksheet is updated for next time. I'd put the
macro code in this central workbook.

dim wb as workbook
dim wbCENTRAL as workbook
Dim ws as worksheet
dim strNAME as string
dim intCOUNT as integer

set wb = ActiveWorkbook
set wbCENTRAL = Workbooks("CENTRAL.xls")
set ws = wbcentral.activesheet

strNAME = ws.cells(1,1).value '''Storage for "CMAC"
intcount = ws.cells(1,2).value '''Storage for Counter

wb.SaveAs("C:\My Documents\" & strname & intcount & ".xls")
intCOUNT = intCOUNT+1
ws.cells(1,2).value = intcount '''Resets the counter for
next time

Cheers

Matthew
 
two possibilities...

1)

Sub SaveFileAs()
Dim sFileName As String
sFileName = Application.GetSaveAsFilename
If sFileName = "False" Then Exit Sub
ActiveWorkbook.SaveAs sFileName
End Sub

2)

Sub SaveFileAs()
Dim sFileName As String
sFileName = InputBox("Enter Name to be saved as...")
If sFileName = "" Then Exit Sub
ActiveWorkbook.SaveAs sFileName
End Sub


just a couple of ideas
you could also add
ActiveWorkbook.Close False
before the End Dub to close the file after saving it.

Patrick Molloy
Microsoft Excel MVP
 
Thak you very much.

One question, though.

Can this be added to the existing macro or does it have to be a
separate one?

Adding "sub" seems to separate them.



------------------------------------------------
Message posted

-- View and post Excel related usenet messages directly from http://www.ExcelForum.com
at http://www.ExcelTip.com/
------------------------------------------------
 
kelleygirl,

You can do it either way.
If you put it into an existing macro, get rid of the Sub xxxx() and End Sub
Or you can call it from a sub by putting in a line

*code here*
Call xxxx
*more code here*

or Run xxxx
(I'm never sure which is correct)

I usually use
ModuleName.MacroName
to be more specific.
I also usually rename my modules with the property window. But never,
never, never, ..... name a module and a macro the same.
 
Back
Top