Create a serial number on the file name

talis

New Member
Joined
Sep 24, 2014
Messages
36
Hi,

I'm looking to create a automatically updating file name. I have created a workbook that is being used as calculator. I need to make the workbook so it is unable to save which I have already done using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ThisWorkbook.Saved = True
Cancel = True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

(this was my first macro code I have ever done to give you an indication of my use level)

I'm now looking to keep this functionality, but I would like the file name to update each time it is opened. this can be done jut with a rolling number.

Can anyone help me cod this?

Thanks!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
what i would suggest is to use Workbook_Open()
write a procedure which saves the workbook as a new one "ThisWorkbook.SaveAs" and then delete the old file if you need to.
 

talis

New Member
Joined
Sep 24, 2014
Messages
36
what i would suggest is to use Workbook_Open()
write a procedure which saves the workbook as a new one "ThisWorkbook.SaveAs" and then delete the old file if you need to.


Maybe I should start off simpler (for my own sake), what if I want to create an incremental number generated on sheet1 whenever the workbook opens, I know this is pretty simple and can be done with


Private Sub Worksheet_Activate() Range("A1") = Range("A1") + 1 Range("B1") = "times opened" 'customize RangeEnd SubBut this code relies on the worksheet being saved. I don't want anyone to be able to alter the worksheet and it should revert to it's original settings other than the incremental number. I'm using the code I posted originally to make the workbook revert to it's original format. But I want to add this incremental number, is there someway of combining the two?</pre>
 

talis

New Member
Joined
Sep 24, 2014
Messages
36
basically I want to add an incremental number to an unsaveable workbook. (for now)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
then write the number to an external file (e.g. *.TXT) and read from and write to it every time you open the workbook
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

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
Top