How to Add a serial number to file name when saving a file

Costa_Mukhar

New Member
Joined
Jun 6, 2015
Messages
20
Hello,

I am using the below code to save the file after running a macro

Dim DateToday As String
DateToday = Format(Now(), "YYYYMMDD")
ActiveWorkbook.SaveAS Filename:="M:\Clients_Active\Arqaam\BBG Reconciliation\BBG_Dividend Fund_" & DateToday & ".xls"

What can I add to the code to add a serial number after the file name, also, I need to reset the serial number on daily basis as well, as I may need to run & save the file more than one time everyday.
For example or something similar:

Today
BBG_Dividend Fund(1)_20150606
BBG_Dividend Fund(2)_20150606
BBG_Dividend Fund(3)_20150606
Tomorrow:
BBG_Dividend Fund(1)_20150607
BBG_Dividend Fund(2)_20150607
BBG_Dividend Fund(3)_20150607

Many thanks for your help in advance.
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Upvote 0
If your willing to add a time component to the file name, it's easy to do and they will automatically be numbered sequentially.

Code:
[COLOR=darkblue]Dim[/COLOR] DateToday [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
DateToday = Format(Now, [COLOR=#ff0000]"YYYYMMDD_HHMMSS"[/COLOR])
ActiveWorkbook.SaveAs filename:="M:\Clients_Active\Arqaam\BBG Reconciliation\BBG_Dividend Fund_" & DateToday & ".xls"
 
Upvote 0
Hi John, thanks for your help, i tried the code in the post you referred to, but it is not working.....do i need to change anything in it? sorry, but i am still a Macro beginner and maybe i am missing something here.....also, i am using Excel 2013, if it matters or any changes required.
 
Upvote 0
Thanks AlphaFrog, i tried adding a time stamp to the file name, but i prefer to add a serial number. like 1,2,3....etc
 
Upvote 0
Yes, the code needs changing slightly, since it is written to save the sequential file names as "Xtemplate.xls", "Xtemplate1.xls", "Xtemplate2.xls", etc.

Here is the code modified for your situation:
Code:
Public Sub Save_As_Next_Serial_Number()

    Dim n As Integer, filename As String
    
    n = 0
    Do
        n = n + 1
        filename = "M:\Clients_Active\Arqaam\BBG Reconciliation\BBG_Dividend Fund(" & n & ")_" & Format(Date, "YYYYMMDD") & ".xls"
    Loop Until Dir(filename) = ""
    
    ActiveWorkbook.SaveAs filename
    
End Sub
 
Upvote 0
Yes, the code needs changing slightly, since it is written to save the sequential file names as "Xtemplate.xls", "Xtemplate1.xls", "Xtemplate2.xls", etc.

[/code]

Thanks a lot John, it works perfectly now. and I know that I had to change the sequential file names and the path that I need, i did that...but i believe I was combining the strings in a wrong way.....appreciate your assistance :)
 
Upvote 0

Forum statistics

Threads
1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top