Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique generated Alphanumeric No.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique generated Alphanumeric No.
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique generated Alphanumeric No.
"eijaz" wrote in message ...
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique generated Alphanumeric No.
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 "GingerTommy" wrote in message om... "eijaz" wrote in message ... 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking to add unique (sequential) number to already generated Use | Excel Worksheet Functions | |||
Assign unique auto-generated number | Excel Discussion (Misc queries) | |||
Counting unique values in a list generated with the OFFSET functio | Excel Worksheet Functions | |||
Finding unique entries among two columns of alphanumeric data | Excel Worksheet Functions | |||
Qualifying a generated value as unique across worksheets | Excel Discussion (Misc queries) |