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
10 977
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 -
Public Sub auto_close()
-
On Error Resume Next
-
Application.ScreenUpdating = False
-
Application.DisplayAlerts = False
-
SaveName= App.Path & "Doc"& Trim(Str(Range("AA1").Value)) & ".xls"
-
Range( "AA1").Value= Range("AA1").Value +1
-
ActiveWorkbook.SaveAs SaveName
-
Application.ScreenUpdating = True
-
Application.Quit
-
End Sub
-
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?
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
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.
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.
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 -
-
i = 0
-
Do
-
i = i + 1
-
strFileName = App.Path & "\Doc" & Trim(Str(i)) & ".xls"
-
Loop Until Dir(strFileName, 0) = vbNullString
-
-
- i = 0
-
Do
-
i = i + 1
-
strFileName = App.Path & "\Doc" & Trim(Str(i)) & ".xls"
-
Loop Until Dir(strFileName, 0) = vbNullString
-
I like it! A nice neat little routine, that.
I like it! A nice neat little routine, that.
isn't it ??
seems the guy needing this is out of order...
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... - ...
-
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.
Huh?
Anyway, I did want to make one very small point about your code, just in case you were aware of it... - ...
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |