473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
10 977
albertw
267 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
Killer42
8,435 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
albertw
267 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
albertw
267 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
Killer42
8,435 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
albertw
267 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
Killer42
8,435 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
albertw
267 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
Killer42
8,435 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
albertw
267 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

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

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
8
by: slim | last post by:
hi again all, i am still working on the website as mentioned in earlier threads and have hit another snag... http://awash.demon.co.uk/index.php http://awash.demon.co.uk/vd.css the php is...
6
by: Jax | last post by:
I have Visual Studio 2002 Standard Edition. It has been working fine up to a point and now i'm at that point. Due to the limitations of the edition i am not using any of my own .dll's and instead...
8
by: intrepid_dw | last post by:
Hello, all. I've created a C# dll that contains, among other things, two functions dealing with byte arrays. The first is a function that returns a byte array, and the other is intended to...
5
by: Andrew | last post by:
Hey all, Requesting help from the VB.Net gurus in here. I was given a task to write a Windows Service (VB.Net) that would run an external program, and if that program closed for any reason...
4
by: Gary | last post by:
Hi, I have a temperature conversion program down pat, but I was told to add an average, meaning, i need to get the average temperature for as many times as it was entered. i do not know where to...
2
by: Steve K | last post by:
I got a bit of a problem I like some help on. I'm designing an online training module for people that work in food processing plants. This is my target audience. These workers have little or no...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
14
namcintosh
by: namcintosh | last post by:
Hello, everyone. Well, let me cut to the chase and explain my problem. I am trying to devise a menu plan that uses the if/else if and the while loop. The program calculates the user's weight...
32
by: =?Utf-8?B?U2l2?= | last post by:
I have a form that I programmatically generate some check boxes and labels on. Later on when I want to draw the form with different data I want to clear the previously created items and then put...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.