We have these Biometric finger scanners here at
work, and it is also used as a time and attendance system.
mainly to check for the occasional late comers.
I wrote a query to query this systems, database (Simple access database)
the database resides on a shared drive and my excel sheet template connect to this database
then queries and gets the first login for the day per user.
first col has the user second the date third col the time
Every thing works fine, But it only shows the users in the log , so if a user did not log in
it means that his or her log wont show late or on time.
So I wrote the below to match the logged users and map it to a textfile that has all the users
per line in a normal .txt file that also resides on a network drive next to the database.
it opens it opens the file and loops thought the logged in users and finds users that is not in the list.
skips to the last line of the excel sheet and adds 3 lines for some space and readability then
list the users that has not logged in for the day per line.
The below code works fine, but is slow as.
It basically scans all lines per user there a many users so, the code is slow.
I would like to optimize it abit, by stepping the "For loop" to read not only one at a time.
I could probably make it read lets say 2 at a time but then if the number of users that
logged in are an "Sorry don't know the word" "One ewige getal" then it will cause to loop to
go crazy.
I'm no programmer I only code when I need it for my self to make life easier.
So please excuse the bad naming and coding practice
Once all this is done it gets mailed to management.
BTW i also use this code for something else as well, with more than 40 000
sometimes i have to find a few mappings not in the list like maybe 5000 or so.
so edit file search wont cut.
Here is the code.
Code: Select all
Dim LineCount As Long
Dim LastLine As Long
Dim NameCount As Long
Dim Username As String
Dim UserInList As Boolean
Dim UserNewLineCount As Long
Sub Main()
LineCount = 0
Do: DoEvents
LineCount = LineCount + 1
If Sheet1.Cells(LineCount, 7) = "1" Then Exit Do
LastLine = LineCount
Loop
UserNewLineCount = LastLine + 5
Sheet1.Cells(LastLine + 4, 1) = "Users that has not loged in for the day"
NameCount = 0
Open "H:\TimeAttend\Users.txt" For Input As #1
Do: DoEvents
NameCount = NameCount + 1
Line Input #1, Username
If EOF(1) Then Exit Do
LineCount = 0
UserInList = False
Do: DoEvents
LineCount = LineCount + 1
If Sheet1.Cells(LineCount, 7) = "1" Then Exit Do
If Username = Sheet1.Cells(LineCount, 1) Then
UserInList = True
Exit Do
Else
UserInList = False
End If
Loop
If UserInList = False Then
UserNewLineCount = UserNewLineCount + 1
If Len(Username) smaller than 3 Then
UserNewLineCount = UserNewLineCount - 1
Else
Sheet1.Cells(UserNewLineCount, 1) = Username
End If
End If
Loop
MsgBox "Done"
Close #1
Columns("A:A").Select
With Selection.Font
.Name = "Tahoma"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("C17").Select
End Sub
Where it sais [If Sheet1.Cells(LineCount, 7) = "1" Then Exit Do]
its the only way i could think of sopping the loop.
in Sheet1.Cells(LineCount, 7) col there is a 1, and it checks each line at col 7
if it finds 1 the loop ends.
When my querry reads the database it gets all data , once done it ads 1 to the next line.
Please help , ask if something is not clear.
Thanks
OMG do you know how much trouble i had to post the above code the little smaller than 3 sign prevented me from posting something about HTML tag not allowed
*Sigh*
that be a tad better though void also works depending on the compiler you are using but then you cannor "return 0"
