Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Loop
Hi,
I have a code to save an Excel sheet into a specific name to a drive. Then if the file exists, my code prompts the user to overwrite the existing file or to create a new file. When the user chooses to create a new file, I want the new files increment by 1 (e.g. test1.xls, test2.xls, etc.). Can someone help me with the code to create those new files by incrementing 1? Can you also limit the new file up to 10 (e.g. test10.xls)? Then prompt the user with a message that you have exceeded the limit. Please help. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Loop
Hi OssieMac,
Thank you very much for your help. Your code works perfectly. Thanks again. "OssieMac" wrote: Hi, This is code that I wrote for someone else on this forum. It automatically increments the number for the save. I have added a msgbox to terminate the process if the number of saves exceeds the required number (currently set at 3 for testing). You will need to do a little more with what you want to do if the number of saves is exceeded. 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 | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |