Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving an excel file with a serially increnmenting number
Hi,
Can you please direct me to code that allows me to save my excel files in a serially incrementing default name, such as book201, book203, book204, etc. as you are aware, excel always go back to book1 whenever you start a new cession, and it doesn't know that the last book was, say book204, and that therefore, it should suggest the next higher number, namely book205. i hope my request is clear. Thanks in advance. RD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving an excel file with a serially increnmenting number
Hi,
Here is a macro that I have posted a couple of time here and I think that it will do what you want. Fee free to get back to me if it need a little tweeking. Sub Save_As_NewName() Dim strInitName As String Dim strNewName As String Dim intAppend As Integer Dim myFile As String Dim strPath 'Set the next line to required file path strPath = Application.DefaultFilePath 'Note next two variables start with same value strInitName = strPath & "\Book1" strNewName = strInitName Do 'Dir function searches for the file description 'which has been concatenated from strNewName 'plus the file extension (First search is for '"C:\Book1.xls") 'if file found then myFile = filename so 'therefore it exists. If not found then myFile 'is empty or = "" myFile = Dir(strNewName & ".xls") 'If myFile is not empty then increment intAppend 'and concatenate it with strInitName and save the 'concatenated string to strNewName so on the first 'loop strNewName would become "C:\Book11". 'Note that strInitName remains unchanged. 'On the next loop if "C:\Book11" exists then 'strNewName would become "C:\Book12". If myFile < "" Then intAppend = intAppend + 1 strNewName = strInitName & intAppend End If If intAppend 3 Then MsgBox "Already " & intAppend & _ " saves of this file" & Chr(13) & _ "Processing terminated" 'Insert your code here to handle the 'excessive saves. End End If 'Next line will cause the code to loop if myfile 'is not empty. If myFile is empty then it has not 'found the most recent strNewName therefore it does 'not exist and can be used as the new file name. Loop While myFile < "" ActiveWorkbook.SaveAs Filename:= _ strNewName, FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:= _ False, CreateBackup:=False End Sub Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving an excel file with a serially increnmenting number
Many thanks and best regards.
RD "OssieMac" wrote in message ... Hi, Here is a macro that I have posted a couple of time here and I think that it will do what you want. Fee free to get back to me if it need a little tweeking. Sub Save_As_NewName() Dim strInitName As String Dim strNewName As String Dim intAppend As Integer Dim myFile As String Dim strPath 'Set the next line to required file path strPath = Application.DefaultFilePath 'Note next two variables start with same value strInitName = strPath & "\Book1" strNewName = strInitName Do 'Dir function searches for the file description 'which has been concatenated from strNewName 'plus the file extension (First search is for '"C:\Book1.xls") 'if file found then myFile = filename so 'therefore it exists. If not found then myFile 'is empty or = "" myFile = Dir(strNewName & ".xls") 'If myFile is not empty then increment intAppend 'and concatenate it with strInitName and save the 'concatenated string to strNewName so on the first 'loop strNewName would become "C:\Book11". 'Note that strInitName remains unchanged. 'On the next loop if "C:\Book11" exists then 'strNewName would become "C:\Book12". If myFile < "" Then intAppend = intAppend + 1 strNewName = strInitName & intAppend End If If intAppend 3 Then MsgBox "Already " & intAppend & _ " saves of this file" & Chr(13) & _ "Processing terminated" 'Insert your code here to handle the 'excessive saves. End End If 'Next line will cause the code to loop if myfile 'is not empty. If myFile is empty then it has not 'found the most recent strNewName therefore it does 'not exist and can be used as the new file name. Loop While myFile < "" ActiveWorkbook.SaveAs Filename:= _ strNewName, FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:= _ False, CreateBackup:=False End Sub Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loosing value of large number when saving file to CSV format | Excel Discussion (Misc queries) | |||
Help with Mr. Peterson's Code.. Print serially from a Sheet | Excel Programming | |||
Help with Mr. Peterson's Code.. Print serially from a Sheet | Excel Programming | |||
how to use Excel to generate alphabet serially | Excel Worksheet Functions | |||
Saving a File Generates a Sequential Number Name | Excel Programming |