By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

Help Needed again

P: 2
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
Share this Question
Share on Google+
10 Replies


100+
P: 267
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 5K+
P: 8,434
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+
P: 267
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+
P: 267
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 5K+
P: 8,434
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+
P: 267
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 5K+
P: 8,434
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+
P: 267
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 5K+
P: 8,434
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+
P: 267
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.