Save File To Folder

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,995
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.
Code:
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

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
Works beautifully, thanks for your help John, greatly appreciated.:):):)
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,995
Similar code, but use Dir(filename, vbDirectory) to check if the folder exists.
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
Drop it into a Do While or Do Until loop - keep checking until you find the next available number:

I.e.
Code:
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

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,995
Try:
Code:
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
 

Forum statistics

Threads
1,081,691
Messages
5,360,644
Members
400,591
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top