Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel autogenerate number when file opened

Posted on 2011-02-17
26
Medium Priority
?
493 Views
Last Modified: 2012-06-27
Hi Guy,

I have no experience with macro programming in excel, but I am trying to figure out, how can I have a excel file opened and it auto generate and increments a number everytime someone opens this file.  it must be unique everytime.  I am using excel 2010.

Thanks
0
Comment
Question by:falimorad
  • 12
  • 10
  • 2
  • +2
26 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919777
There are many ways you can do it. If it is just for a particular workbook then I would suggest you to create a temp sheet and store the number there and every time you open the workbook simply read that value and increment it.

Sid
0
 
LVL 3

Expert Comment

by:Sleetish
ID: 34919781
Excel has a random generator function.

http://support.microsoft.com/kb/828795 
0
 
LVL 15

Expert Comment

by:gplana
ID: 34919792
If it is not important that the number follows a sequence, so if the only important is to be unique everytime, you can use this formula on the ceil:

= Int(Rand()*2000000000)

Otherwise you should program a macro on the open event to take a numer on a cell (i.e. A1) increment it and save the number on the cell A1, but this is a little more complex.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919837
This is what I was suggesting. Sample file attached.

Sid

Code Used

Dim Numb As Long

Private Sub Workbook_Open()
    Numb = Sheets("Temp").Range("A1").Value + 1
    msgbox Numb
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Temp").Range("A1").Value = Numb
End Sub

Open in new window

Sample.xls
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34920647
You state an incrementing number so the following does just that when placed in the thisworkbook module.  Basically it initialises a customdocumentproperty if needed and stores the number there.  The script includes a msgbox for demo purposes and a save as soon as the file opens so the increment is recorded.

Both the auto save and the msgbox can be deleted when you are happy

The attached file has the code and has been initialised .. was set at 2 at closure of test

Chris
Private Sub Workbook_Open()
    If Not propexists("Ticker") Then
        Me.CustomDocumentProperties.Add Name:="Ticker", LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:=0, LinkSource:=False
    End If
    Me.CustomDocumentProperties("Ticker") = Me.CustomDocumentProperties("Ticker") + 1
    Me.Save
    MsgBox Me.CustomDocumentProperties("Ticker")
End Sub

Function propexists(strPropname) As Boolean
Dim prop As Object

    For Each prop In Me.CustomDocumentProperties
        If LCase(prop.Name) = LCase(strPropname) Then
            propexists = True
            Exit For
        End If
    Next
End Function

Open in new window

Workbook1.xls
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34920659
BTW, recall the earlier statement the code self initialises so you simply put the supplied code in the thisworkbook and it works from the time it is first saved and then re-opened.

Chris
0
 

Author Comment

by:falimorad
ID: 34925741
Thanks Chris, I have already a excel file, and there is a cell where it has invoice number, and this is where I want the number to be generated on, and is there a place where I can store this number?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34925844
Did you check my post?

Sid
0
 

Author Comment

by:falimorad
ID: 34925894
Yes I did, do you have more description, or a site I can see how that is done.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34925907
If you open the file that I attached then you can see the code. Or if you have a sample file then maybe I can give you an example?

Sid
0
 

Author Comment

by:falimorad
ID: 34925914
Sorry I was just reading your first post, let me look more into the second
0
 

Author Comment

by:falimorad
ID: 34926001
hey Siddharthrout, so would it be random and unique everytime.  How do I implement that in to my worksheet.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926014
Do you want the number to be random or increment it every time?

Sid
0
 

Author Comment

by:falimorad
ID: 34926036
Lets keep it incrementing for simplicity.  Most important it has to be different everytime it is opened, and is there a way for the number to be stored
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926051
Then the file that I gave above does what you want :)

What is the initial number that you want the series to start with?

Sid
0
 

Author Comment

by:falimorad
ID: 34926107
It doesnt matter it can start with 1, but how come everytime I save it and reopen it, it gives the same number?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926144
Are macros enabled?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926177
The reason why I ask is because every time I open it, it gives me a new number, provided I save the file before closing...

Sid
0
 

Author Comment

by:falimorad
ID: 34926250
OK when I close and save, it increments. What do I replace msgbox Numb, in order for that to be displayed on a particular cell. Is there a way to increment even if the document is not saved, where it is just closed.  I see how you have in the temp worksheet the number that is going to be used to increment, is there a possible way we can have another workbook, where it stored all the numbers that have been used.  After it closes it increments the temp worksheet, and stored it in another worksheet, so we have a track of every number used?

Thanks for all your help, you are very helpful
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926355
What do I replace msgbox Numb, in order for that to be displayed on a particular cell

Say you want to display the number in cell A1 of sheet1 then Replace the line

msgbox Numb

with

Sheets("Sheet1").Range("A1").Value = Numb

Is there a way to increment even if the document is not saved, where it is just closed.

Yes. Delete the code Workbook_BeforeClose and Use this code

Dim Numb As Long

Private Sub Workbook_Open()
    Numb = Sheets("Temp").Range("A1").Value + 1
    Sheets("Sheet1").Range("A1").Value = Numb
    Sheets("Temp").Range("A1").Value = Numb
End Sub

Open in new window


I see how you have in the temp worksheet the number that is going to be used to increment, is there a possible way we can have another workbook, where it stored all the numbers that have been used.  After it closes it increments the temp worksheet, and stored it in another worksheet, so we have a track of every number used?

Yes you can do that as well.

You will then have to use this code... Autonumber.xls is the other workbook where the numbers are stored.

Dim Numb As Long

Private Sub Workbook_Open()
    Dim wb1 As Workbook, wb2 As Workbook
    
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open("C:\Autonumber.xls")
    
    Numb = wb2.Sheets("Temp").Range("A1").Value + 1
    
    wb1.Sheets("Sheet1").Range("A1").Value = Numb
    
    wb2.Sheets("Temp").Range("A1").Value = Numb
    
    wb2.Close savechanges:=True
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34926383
I am going out for a couple of hours so I wont be able to reply to your queries now. But I will, once I get back  :)

However, I think I have answered all your main queries :)

Take Care,

Sid
0
 

Author Comment

by:falimorad
ID: 34929408
Thanks Sid, I tried having an autonumber file, but it doesnt store all the numbers, it just stores the number in A1 cell and gets updated with the new value.  I decided to go with

Dim Numb As Long

Private Sub Workbook_Open()
    Numb = Sheets("Temp").Range("A1").Value + 1
    Sheets("Sheet1").Range("A1").Value = Numb
    Sheets("Temp").Range("A1").Value = Numb
End Sub

The problem is, It will only increment when I save, close and reopen.  It wont increment if I just close and reopen.

So there isn't a way to store the numbers used on a separate file or tab so that the numbers will be stored in A1 then the second number would be A2, A3... and so on?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 34930552
The problem is, It will only increment when I save, close and reopen.  It wont increment if I just close and reopen.

To overcome that, immediately save the workbook via code when you open it. For example

Dim Numb As Long

Private Sub Workbook_Open()
    Numb = Sheets("Temp").Range("A1").Value + 1
    Sheets("Sheet1").Range("A1").Value = Numb
    Sheets("Temp").Range("A1").Value = Numb
    ActiveWorkbook.Save
End Sub

Open in new window


So there isn't a way to store the numbers used on a separate file or tab so that the numbers will be stored in A1 then the second number would be A2, A3... and so on?

Yes that is possible but then if you are using Excel 2003 then the maximum numbers you can store is 65536 and in Excel 2007/2010 is 1048576. If you are sure that you will not reach those limits then try this

Dim Numb As Long

Private Sub Workbook_Open()
    Dim lastRow As Long
    
    lastRow = Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
    
    Numb = Sheets("Temp").Range("A" & lastRow).Value + 1
    
    Sheets("Sheet1").Range("A1").Value = Numb
    
    Sheets("Temp").Range("A" & lastRow + 1).Value = Numb
    ActiveWorkbook.Save
End Sub

Open in new window


Sample file attached.

Sid

AutoNumber.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34930555
Here is the 2003 version.

Sid
AutoNumber.xls
0
 

Author Comment

by:falimorad
ID: 34953202
Sid your the best, thanks
0
 

Author Closing Comment

by:falimorad
ID: 34953207
Best solution
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question