Unique generated Alphanumeric No.

  • Thread starter Thread starter eijaz
  • Start date Start date
E

eijaz

Hi All,

1) Is it possible to generate a Unique No Code (e.g RV00001) in a cell so
that whoever accesses that file and when he saves it as some other filename,
gets a unique no on his excel copy & that unique no is also retained as the
last-no-used on the original excel sheet.

2) Now whatever he entered will be automatically entered on a diff
workbook's worksheet called say the 'Database' & in a database like manner,
i.e Records(rows of records).

I am new to VBA & not quite used to it. See my post "Creating a Database" as
of nov 15th.

Could anybody help me on this?

Thanking you in advance,


Eijaz
 
Is there a reason why you can't just dedicate a cell in a
read only workbook. Add 1 within the initial
Public Sub Workbook_Open()
End Sub
You could, theoretically create a shortcut on a desktop
thatopens an excel file automatically in read only mode
using
/r workbook_path/file_name
 
eijaz said:
1) Is it possible to generate a Unique No Code (e.g RV00001) in a cell so
that whoever accesses that file and when he saves it as some other filename,
gets a unique no on his excel copy
Click to expand...

This code will put a globally unique identifier in a cell in Sheet1
prior to saving the workbook. Place the code in the Workbook code
module:


Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID)
As Long

Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

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

Sheet1.Cells(1).Value = GetGUID
End Sub

Private Function GetGUID() As String
Dim udtGUID As GUID

If (CoCreateGuid(udtGUID) = 0) Then
GetGUID = _
String(8 - Len(Hex$(udtGUID.Data1)), "0") &
Hex$(udtGUID.Data1) & _
String(4 - Len(Hex$(udtGUID.Data2)), "0") &
Hex$(udtGUID.Data2) & _
String(4 - Len(Hex$(udtGUID.Data3)), "0") &
Hex$(udtGUID.Data3) & _
IIf((udtGUID.Data4(0) < &H10), "0", "") &
Hex$(udtGUID.Data4(0)) & _
IIf((udtGUID.Data4(1) < &H10), "0", "") &
Hex$(udtGUID.Data4(1)) & _
IIf((udtGUID.Data4(2) < &H10), "0", "") &
Hex$(udtGUID.Data4(2)) & _
IIf((udtGUID.Data4(3) < &H10), "0", "") &
Hex$(udtGUID.Data4(3)) & _
IIf((udtGUID.Data4(4) < &H10), "0", "") &
Hex$(udtGUID.Data4(4)) & _
IIf((udtGUID.Data4(5) < &H10), "0", "") &
Hex$(udtGUID.Data4(5)) & _
IIf((udtGUID.Data4(6) < &H10), "0", "") &
Hex$(udtGUID.Data4(6)) & _
IIf((udtGUID.Data4(7) < &H10), "0", "") &
Hex$(udtGUID.Data4(7))
End If
End Function


This may not be axactly what you're looking for, but hopefully gives
you a start.

Cheers, Thomas.
 
Thanks you,

Its certainly a very good example, but i want something not generated
randomly in a cell, but which can be incremented in a cell everytime a
workbook is saved.

this will help in properly sorting it in ascending or descending order.
thats why i want it in the format "RV - 000001". the next time, workbook is
saved, it will increment to "RV - 000002" & so on..............till limit :
"RV - 999999".

Secondly, if you see my posting, it has another query to it & that is saving
anything which was entered (including the generated unique #) to another
worksheet (same workbook or another workbook) as records in rows, so as to
keep a track record of who has saved/printed the worksheet.
e.g.
RV# | NAME | DT_PREPARED | DT_EXPENSE_INCURRED |
EXPENSE_AMT |

Please kindly refer my posting "Creating an Excel Database" to understand
exactly what i want.

Could you please help me on this?

Thanks & best regards,

Eijaz


eijaz said:
1) Is it possible to generate a Unique No Code (e.g RV00001) in a cell so
that whoever accesses that file and when he saves it as some other filename,
gets a unique no on his excel copy
Click to expand...

This code will put a globally unique identifier in a cell in Sheet1
prior to saving the workbook. Place the code in the Workbook code
module:


Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID)
As Long

Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

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

Sheet1.Cells(1).Value = GetGUID
End Sub

Private Function GetGUID() As String
Dim udtGUID As GUID

If (CoCreateGuid(udtGUID) = 0) Then
GetGUID = _
String(8 - Len(Hex$(udtGUID.Data1)), "0") &
Hex$(udtGUID.Data1) & _
String(4 - Len(Hex$(udtGUID.Data2)), "0") &
Hex$(udtGUID.Data2) & _
String(4 - Len(Hex$(udtGUID.Data3)), "0") &
Hex$(udtGUID.Data3) & _
IIf((udtGUID.Data4(0) < &H10), "0", "") &
Hex$(udtGUID.Data4(0)) & _
IIf((udtGUID.Data4(1) < &H10), "0", "") &
Hex$(udtGUID.Data4(1)) & _
IIf((udtGUID.Data4(2) < &H10), "0", "") &
Hex$(udtGUID.Data4(2)) & _
IIf((udtGUID.Data4(3) < &H10), "0", "") &
Hex$(udtGUID.Data4(3)) & _
IIf((udtGUID.Data4(4) < &H10), "0", "") &
Hex$(udtGUID.Data4(4)) & _
IIf((udtGUID.Data4(5) < &H10), "0", "") &
Hex$(udtGUID.Data4(5)) & _
IIf((udtGUID.Data4(6) < &H10), "0", "") &
Hex$(udtGUID.Data4(6)) & _
IIf((udtGUID.Data4(7) < &H10), "0", "") &
Hex$(udtGUID.Data4(7))
End If
End Function


This may not be axactly what you're looking for, but hopefully gives
you a start.

Cheers, Thomas.
 
Back
Top