Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Create Unique number from a date & time

Posted on 2001-06-11
Medium Priority
181 Views
We are scanning enrolment forms with VB software we have written. currently our files are named;

S:\studentRef\studentRef x nextIndex .jpg

eg .

\00000801
00000801x1.jpg
00000801x2.jpg
00000801x3.jpg
00000801x4.jpg

As we have scanning sites out of our physical network its possible that the same filename will occur. We have written software to syncronize the directories  so not to overwrite any files ... essentially to increment the index until a valid one is found in the directory.

We've found this to be a slow process as we will be dealing with over 25 thousand directories at the end of each year.
..........

Our idea is to create a unique number from the date and time to eliminate this problem.

So, can someone supply us with some fancy code to create a number generated from the date and time?

or provide a better solution.

Thanks.

Gaz
0
Question by:Grunge
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 3
• 3
• +8

LVL 6

Expert Comment

ID: 6177994
you could use this:

strFileName = "C:\Dir1\Dir2\" & Replace(CStr(Date), "/", "") & ".jpg"

and for today you would get a file like
C:\Dir1\Dir2\6112001.jpg

hope that helps!
0

LVL 6

Expert Comment

ID: 6178053
Try that:

Dim DateTime As Date
Dim Nr As Long
Dim FileName As String

DateTime = "31.12.9999 23:59:59"

Nr = 0
Nr = Nr + CLng(Second(DateTime))
Nr = Nr + 60& * CLng(Minute(DateTime))
Nr = Nr + 60& * 60& * CLng(Hour(DateTime))
Nr = Nr + 60& * 60& * 24& * CLng(Day(DateTime))
Nr = Nr + 60& * 60& * 24& * 31& * CLng(Month(DateTime))

FileName = Format(Year(DateTime), "0000") & Nr

Debug.Print FileName
0

LVL 6

Expert Comment

ID: 6178064
Try that:

Dim DateTime As Date
Dim Nr As Long
Dim FileName As String

DateTime = "31.12.9999 23:59:59"

Nr = 0
Nr = Nr + CLng(Second(DateTime))
Nr = Nr + 60& * CLng(Minute(DateTime))
Nr = Nr + 60& * 60& * CLng(Hour(DateTime))
Nr = Nr + 60& * 60& * 24& * CLng(Day(DateTime))
Nr = Nr + 60& * 60& * 24& * 31& * CLng(Month(DateTime))

FileName = Format(Year(DateTime), "0000") & Nr

Debug.Print FileName
0

LVL 6

Expert Comment

ID: 6178068
soory, every time i press the back-button in my browser, the comment is sent (twice) :-)

sorry
0

LVL 6

Expert Comment

ID: 6178078
to John Fish:

what is with:

1\11\2001 --> 11120
11\1\2001 --> 11120

0

LVL 6

Expert Comment

ID: 6178084
damn:

1\11\2001 --> "1112001"
11\1\2001 --> "1112001"

0

LVL 6

Expert Comment

ID: 6178090
in my example every second in a year is represented by a long value:

0 to 34905599

the year precedes with exactl 4 digits.
0

LVL 6

Expert Comment

ID: 6178121
another idea:

1.

use

Public Declare Function GetTempFilename Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long

to create a unique file

2. store the filename
3. kill that file
0

LVL 6

Expert Comment

ID: 6178163
>>JonFish what is with<<

He wanted a random number generated based on the time, so I figured that you could get the date (1/11/2001 for example) and replace the "/" with nothing = 1112001 (or 11120 depending on your date settings). However, I overlooked the fact of that possibility (1/11 and 11/1). Your solution is more failsafe :-)
0

LVL 5

Expert Comment

ID: 6178193
some info on gettempfilename function :

Declare Function GetTempFileName& Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String)

LpszPath -- string - a directory to use for the temporary file. Typically determined using the GetTempPath function.

LpPrefixString ? string - the file name prefix to use.
The first three characters are used as the file name prefix.

WUnique -- long - a number to append to the prefix string. If zero, this function will generate a file name using a random number. It will then see if a file by that name exists. If it does, the function will increment the number and continue with the attempt until a unique file name is generated. The file will remain on the drive with a length of zero bytes. If not zero, the file will not be created and the function will not check to see that it is a unique file name.

LpTempFileName -- string - A buffer to load with the new temporary file name. This buffer should be at least MAX_PATH characters long.
0

LVL 5

Expert Comment

ID: 6178202
you can do it with one line.
try this : debug.Print format(now,"yyyymmddhhnnss")
0

LVL 3

Expert Comment

ID: 6178353
What about appending a unique ID for each location, workstation or user?  That way, on the off chance that two people scan a file at exactly the same second, they will still come up with unique file names?  You could generate this unique suffix automtically using some unique identifier from the workstation (i.e. IP address, user id of the person logged in, workstation name, etc...)
0

LVL 7

Expert Comment

ID: 6178450
Seeing as how this will be an automated process, it is very probable that two files will be generated in the same second. You may want to investigate the API GetTicks (or whatever it is called) to get a more precise time reading.

Zaphod.
0

LVL 5

Expert Comment

ID: 6178482
ANOTHER SOLUTION USE GUID :

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

Dim retGUID As typGUID
Declare Function CoCreateGuid Lib "OLE32.DLL" (retGUID As typGUID) As Long

Function CreateGUID() As String

'Create a GUID.
Dim Rc As Long
Dim bytGUID() As Byte
Dim strGUID1 As String
Dim strGUID2 As String
Dim strGUID3 As String
Dim strGUID As String
Dim i As Integer

Rc = CoCreateGuid(retGUID)
If Rc = 0 Then
strGUID1 = Hex\$(retGUID.Data1)
strGUID2 = Hex\$(retGUID.Data2)
strGUID3 = Hex\$(retGUID.Data3)

strGUID = strGUID1 & "-" & strGUID2 & "-" & strGUID3 & "-"
strGUID1 = ""

For i = 0 To 7
strGUID1 = strGUID1 & Format\$(Hex\$(retGUID.Data4(i)), "00")
Next i
CreateGUID = strGUID & Mid\$(strGUID1, 1, 4) & "-" & Mid\$(strGUID1, 5)
Else
Err.Raise vbObjectError + 512 + 5, , "Cann;t create GUID"  ' OR SOMETHING ELSE

End If

End Function

0

LVL 6

Expert Comment

ID: 6178516
rkot2000, you have proposed an answer when several other expert comments are just as viable as solutions. Please read the EE guidlines about proposing answers here at EE. Most experts choose to comment over proposing an answer as it locks the question, and moves it to the Answered Questions list.

Grunge, I would suggest that you reject the proposed answer, and choose from one of the others which also provide a solid solution.

0

Expert Comment

ID: 6178646
Open MyLogFile For Input As #1
Open "c:\Students_" & Format(Now(), "yyyy-mm-dd") & ".jpg"

.. then the rest of your save code..
0

LVL 4

Expert Comment

ID: 6179277
How often do you collect the remote data?

It may not be necessary to date stamp - but rather to get the time more accurately represented.

I would also suggest putting an additional digit into the name to identify the scanning source - and perhaps a check digit to make sure no-one has fiddled with the filename in transit.

With these latter enhancements, you can also handle the unlikely event of two scans happening in diverse locations at the same time....   (e.g. teo ro more sites start scanning at the start of work each day - so there will be a high density of scans around 0900).

0

LVL 3

Accepted Solution

Hornet241 earned 400 total points
ID: 6179402

You could try this

Filename = Environ("Computername) & format(date,"mm-dd-yyyy") & format(time, "hhmmss")

this will give you a unique filename unless a user can scan two files within one second or there are two computers with the same name

Environ("USERDOMAIN")
just in case
0

LVL 9

Expert Comment

ID: 6180649
ping..
0

Expert Comment

ID: 6400324

Experts, which answer/comment do you feel that should be accepted here?

Thanks

costello
Community Support Moderator @ Experts-Exchange
0

LVL 1

Author Comment

ID: 6798937
Thanks all!.. a very busy thread at the time.

Ive decided to use the basics of Hornet241's idea for my solution.

I would have like to split the points, but EE are still working on that facility.

.........................................................

what i did in the end.

'*******************************************************

Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal sBuffer As String, lSize As Long) As Long

Private Sub Command1_Click()
' L for Learning Agreement Forms
' R for Reenrolment Forms
Text1.Text = UniqueFilename("00000801", "L")
End Sub

Public Function UniqueFilename(Sref As String, FormType As String) As String
Dim NameSize As Long
Dim MachineName As String
Dim X As Long
MachineName = Space\$(16)
NameSize = Len(MachineName)
X = GetComputerName(MachineName, NameSize)
UniqueFilename = FormType & "_" & Sref & "_" & Format(Now, "yyyymmddhhnnss") & "_" & MachineName
End Function
0

## Featured Post

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describesâ€¦
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Uâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦
###### Suggested Courses
Course of the Month7 days, 7 hours left to enroll