467,161 Members | 895 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Help Needed again

Hi guys, last help was awesom, worked a treat.

need some more help

does anyone know the code for a macro to save and then close down excel. but saving in increasing incriments.

ie first time its doc1.xls
2nd time its called doc2.xls
etc.

cheers
Nov 20 '06 #1
  • viewed: 893
Share:
10 Replies
100+
Hi guys, last help was awesom, worked a treat.

need some more help

does anyone know the code for a macro to save and then close down excel. but saving in increasing incriments.

ie first time its doc1.xls
2nd time its called doc2.xls
etc.

cheers
hi

suggest your have a counter running on your spreadsheet (hidden)
e.g: cell AA1

Expand|Select|Wrap|Line Numbers
  1. Public Sub auto_close()
  2. On Error Resume Next
  3. Application.ScreenUpdating = False
  4. Application.DisplayAlerts = False
  5. SaveName= App.Path & "Doc"& Trim(Str(Range("AA1").Value)) & ".xls"
  6. Range( "AA1").Value= Range("AA1").Value +1
  7. ActiveWorkbook.SaveAs SaveName
  8. Application.ScreenUpdating = True
  9. Application.Quit
  10. End Sub
  11.  
Nov 20 '06 #2
Expert 8TB
hi
suggest your have a counter running on your spreadsheet (hidden)
e.g: cell AA1
Wouldn't it be simpler to just take the current file name and modify it?
Nov 20 '06 #3
100+
Wouldn't it be simpler to just take the current file name and modify it?
hi

your absolutely right

like

Sub auto_close()
Oldname = ActiveWorkbook.Name
NewName = "Doc" & Trim(Str(Val(Right(Oldname, Len(Oldname) - 3) + 1)))
ActiveWorkbook.SaveAs NewName
End Sub
Nov 20 '06 #4
100+
hi

your absolutely right

like

Sub auto_close()
Oldname = ActiveWorkbook.Name
NewName = "Doc" & Trim(Str(Val(Right(Oldname, Len(Oldname) - 3) + 1)))
ActiveWorkbook.SaveAs NewName
End Sub
hi

additionally
if you consider to use the last code, need to start your application with the latest sheet, otherwise you get trouble assigning names
while the first option gives you always the initial speadsheet and will write files in numeric order.
Nov 21 '06 #5
Expert 8TB
if you consider to use the last code, need to start your application with the latest sheet, otherwise you get trouble assigning names
while the first option gives you always the initial speadsheet and will write files in numeric order.
There are other options, of course (aren't there always).

For instance, you could check for which names already exist on disk, and generate the next one in the series. That way, you'll always add onto the end, with little danger of overwriting.
Nov 21 '06 #6
100+
There are other options, of course (aren't there always).

For instance, you could check for which names already exist on disk, and generate the next one in the series. That way, you'll always add onto the end, with little danger of overwriting.
sure

Expand|Select|Wrap|Line Numbers
  1.  
  2. i = 0
  3. Do
  4. i = i + 1
  5. strFileName = App.Path & "\Doc" &  Trim(Str(i)) & ".xls"
  6. Loop Until Dir(strFileName, 0) = vbNullString
  7.  
  8.  
Nov 21 '06 #7
Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. i = 0
  2. Do
  3.   i = i + 1
  4.   strFileName = App.Path & "\Doc" &  Trim(Str(i)) & ".xls"
  5. Loop Until Dir(strFileName, 0) = vbNullString
  6.  
I like it! A nice neat little routine, that.
Nov 21 '06 #8
100+
I like it! A nice neat little routine, that.
isn't it ??
seems the guy needing this is out of order...
Nov 21 '06 #9
Expert 8TB
isn't it ??
seems the guy needing this is out of order...
Huh?


Anyway, I did want to make one very small point about your code, just in case you were aware of it...
Expand|Select|Wrap|Line Numbers
  1. ...
  2. strFileName = App.Path & "\Doc" & Trim(Str(i)) & ".xls"
The expression Format(i) would return the same as Trim(Str(i)), with one less function call.
Nov 21 '06 #10
100+
Huh?


Anyway, I did want to make one very small point about your code, just in case you were aware of it...
Expand|Select|Wrap|Line Numbers
  1. ...
  2. strFileName = App.Path & "\Doc" & Trim(Str(i)) & ".xls"
The expression Format(i) would return the same as Trim(Str(i)), with one less function call.
referred to adamme3 only
tnx 4 yr code-update
Nov 21 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by netpurpose | last post: by
6 posts views Thread by Jax | last post: by
8 posts views Thread by intrepid_dw@hotmail.com | last post: by
5 posts views Thread by Andrew | last post: by
4 posts views Thread by Gary | last post: by
2 posts views Thread by Steve K | last post: by
15 posts views Thread by Jay | last post: by
32 posts views Thread by =?Utf-8?B?U2l2?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.