Save File To Folder

[Jaye7]

Help would be appreciated.

I would like a script which would save my file named Xtemplate to a folder named C:\Temp\ExcelTemplates\

The only thing is I would like it if the script could add a new number to the file name if the file already exists in the destination folder.

I.e. Xtemplate already exists so save file as
Xtemplate1 (if Xtemplate1 exists, save as)
Xtemplate2 (if etc... etc....)

 

[John_w]

Try this. The .xls extension assumes you want to save as an Excel 2003 Workbook. Use the Macro Recorder to discover the VBA to Save As other Excel file types or versions.

Sub Save_As_Next_Sequence2()

    Dim n As Integer
    Dim filename As String
    
    n = 0
    Do
        filename = "C:\Temp\ExcelTemplates\Xtemplate" & IIf(n = 0, "", n) & ".xls"
        n = n + 1
    Loop Until Dir(filename) = ""
    
    ActiveWorkbook.SaveAs filename
    
End Sub

 

[Jaye7]

Works beautifully, thanks for your help John, greatly appreciated.

 

[Jaye7]

John,

Could you please provide a similar script that would work for a folder.

I.e. I run a macro and it creates a folder named C:\temp, if temp already exists then it creates a folder named C:temp2 etc...

Thank you

 

[John_w]

Similar code, but use Dir(filename, vbDirectory) to check if the folder exists.

 

[Jaye7]

Hi John,

Sorry for the hassles but I tried changing the code and couldn't get it to do what I wanted.

Basically I just want to create a folder in the C drive but if the folder already exists then it will create the folder with a number at the end.

i.e. MkDir "C:\Temp"

if temp already exists then it will make

MkDir "C:\Temp2"

if temp2 already exists then it will make

MkDir "C:\Temp3"

etc... etc....

Thank you

 

[xenou]

Drop it into a Do While or Do Until loop - keep checking until you find the next available number:

I.e.

Dim i As Integer
Dim s As string
s = "Temp"
If File_Exists("C:\Folder\" & s & ".xls") then
Do Until File_Exists("C:\Folder\" & s & ".xls") = False
    i = i +1
    s = Temp & i
Loop

In the above example you will need to create or find a "file exists" function of which numerous examples can be found.

 

[Jaye7]

Thanks for your reply Xenou but the script isn't working for me, it is locking up on the file_exists part, I am using excel 2000 version.

Also this script should only be based on creating a folder and if it exists then it will add a number to the folder name etc... xls was to do with the first code that John helped on, I just wanted John to adjust it to create a folder in stead of saving the workbook.

 

[xenou]

Okay, replace file_exists with folder_exists. As noted previously, that is a custom function you must create yourself - are you able to do that?

 

[John_w]

Try:

Sub Create_Next_Folder_Sequence()

    Dim n As Integer
    Dim folderPath As String
    
    n = 0
    Do
        folderPath = "C:\Temp" & IIf(n = 0, "", n)
        n = n + 1
    Loop Until Dir(folderPath, vbDirectory) = ""
    
    MkDir folderPath
    
End Sub