Saving an Excel file with 12-NNN (increasing number)

**bmatsell** · 05-25-2012, 01:03 PM

Hello,

I need help tyring to duplicate a formula and apply it to another document. I would like to have an excel file, when saved create a number in a cell with the last 2 digits of year 12-NNN and the NNN be increasing from 001. I have attached the document I would like to duplicate the formula in cell H2. Can't figure it out. Any help would be appreciated.

Thanks

**bmatsell** · 05-25-2012, 01:53 PM

I will add that the document gets saved to SharePoint. Also, I have attached a copy of the documnet I would like to copy that formula into. The cell I want to copy it into is A3.

**Medpack** · 05-25-2012, 02:08 PM

So this file would overwrite the previous file, e.g. 11-002 would replace 11-001? Or is each automatically created file separate from the previous file.

Sounds like you want a source document that when you click save, will save the file as the next number in order, and then also upload it to sharepoint, correct?

**Might also add that there is no event currently in Excel that will trigger solely off the "save" button - e.g. you'll have to figure out a different event to trigger this - either a button with the macro assigned that you click when you want to upload/save to sharepoint or you can write the macro to trigger when the document is closing.

*** Might also add one more thing... you can view the hidden "FORMULAS" sheets, by going into the vba editor (alt + f11), and then viewing the immediate window and executing the following code:

excel.ActiveWorkbook.Sheets("Formulas").visible = true

**bmatsell** · 05-25-2012, 02:10 PM

Correct. When I click save, the file will save as the next number in order and then upload it to sharepoint.

**Medpack** · 05-25-2012, 02:42 PM

Ok, well again, you unfortunately can't execute events soley off the default save button for Excel. However, you can create a custom "save" button, or have the event triggered off a different event (e.g. when the document closes).

The code would look something like the following to upload to sharepoint & save as the next number in line.

Sub SaveAsNext()
On Error Resume Next
Activeworkbook.CustomDocumentProperties.Add Name:="NNN", LinkToContent:=False, Value:=1, Type:=msoPropertyTypeString
On Error GoTo 0
If ActiveWorkbook.CustomDocumentProperties("NNN").Value <> 1 Then
ActiveWorkbook.CustomDocumentProperties("NNN").Value = ActiveWorkbook.CustomDocumentProperties("NNN").Value + 1
End if
ActiveWorkbook.Save
Dim NNNValue As String
Select Case Len(ActiveWorkbook.CustomDocumentProperties("NNN").Value)
Case Is = 1
NNNValue = "00" & ActiveWorkbook.CustomDocumentProperties("NNN").Value
Case Is = 2
NNNValue = "0" & ActiveWorkbook.CustomDocumentProperties("NNN").Value
Case Is = 3
NNNValue = ActiveWorkbook.CustomDocumentProperties("NNN").Value
End Select
ActiveWorkbook.SaveAs "INSERT YOUR SHAREPOINT PATH HERE AND END WITH A \" & Mid(Year(now), 3, 2) & "-" & NNNValue & ".xlsx" 'Use xls for 2003 excel, or xlsx for 2010
'You can get your sharepoint path by going to the folder you want the file to upload in, hand upload a sample file, then click on the file and click "send link" - This will open outlook and create an email with your full sharepoint path and file name - copy everything up to the last slash
ActiveWorkbook.CheckIn savechanges:=True, Comments:="", MakePublic:=""
End Sub

**bmatsell** · 05-25-2012, 06:42 PM

Thank you for the help. The only problem is that the formula keeps adding just a one at the end of the name. 12-0131. I also want to add one digit on the end of the naming convention. What I am trying to do is increase the last number by like 12-013 is the first one and when i save a new one i want it to be 12-014.