OTWarrior
01-21-2011, 09:04 AM
Excel 2003
I am trying to import a login text file from our database, in order to see who if currently logged in. It's in a similar format to this:
|~> 19/01/2011|09:36:38 / v3.63.2 Bob
|~> 19/01/2011|09:37:19 / v3.63.2 John
|~> 19/01/2011|09:39:46 / v3.63.2 Steve
|~> 19/01/2011|09:40:58 / v3.63.2 Jack
<~| 19/01/2011|09:57:17 / v3.63.2 Bob
|~> 19/01/2011|09:57:58 / v3.63.2 Bob
|~> is log in
<~| is log out
Ideally, I would like to have the names already set up on the spreadsheet, using excel to search the text file for today's date, find the person, find the latest row of that person and read if they were last logging in or out. Then change the value of the cell that has their name next to it, etc.
This is the code I have so far:
Public Sub IsUserLoggedIn()
Dim databaseFileName As String
Dim TextLine As String
Dim ifile As Integer
Dim i As Integer
On Error GoTo err_handle
databaseFileName = "\\Database\Main_LoginFile.log"
i = 0
If FileExists(databaseFileName) Then
Open (databaseFileName) For Input As #ifile 'Open file.
Do While Not EOF(ifile) 'Loop until end of file.
i = i + 1
Line Input #ifile, TextLine 'Read line into variable.
If Len(TextLine) > 0 Then 'Inputline greater than Zero length
'// filter not used //
Range("a" & i).Select
ActiveCell.Value = TextLine 'this is for testing if it read ok, will change later
End If
Loop
Close #ifile
End If
Exit Sub
err_handle:
MsgBox Err.Description
End Sub
Public Function FileExists(ByVal DirectoryAndFileName) As Boolean
On Error Resume Next
FileExists = (Len(Dir(DirectoryAndFileName, vbDirectory + vbHidden + vbSystem)) > 0)
End Function
This line:
Open (databaseFileName) For Input As #ifile
...fails, saying bad filename.
Am I on the right track?
I am trying to import a login text file from our database, in order to see who if currently logged in. It's in a similar format to this:
|~> 19/01/2011|09:36:38 / v3.63.2 Bob
|~> 19/01/2011|09:37:19 / v3.63.2 John
|~> 19/01/2011|09:39:46 / v3.63.2 Steve
|~> 19/01/2011|09:40:58 / v3.63.2 Jack
<~| 19/01/2011|09:57:17 / v3.63.2 Bob
|~> 19/01/2011|09:57:58 / v3.63.2 Bob
|~> is log in
<~| is log out
Ideally, I would like to have the names already set up on the spreadsheet, using excel to search the text file for today's date, find the person, find the latest row of that person and read if they were last logging in or out. Then change the value of the cell that has their name next to it, etc.
This is the code I have so far:
Public Sub IsUserLoggedIn()
Dim databaseFileName As String
Dim TextLine As String
Dim ifile As Integer
Dim i As Integer
On Error GoTo err_handle
databaseFileName = "\\Database\Main_LoginFile.log"
i = 0
If FileExists(databaseFileName) Then
Open (databaseFileName) For Input As #ifile 'Open file.
Do While Not EOF(ifile) 'Loop until end of file.
i = i + 1
Line Input #ifile, TextLine 'Read line into variable.
If Len(TextLine) > 0 Then 'Inputline greater than Zero length
'// filter not used //
Range("a" & i).Select
ActiveCell.Value = TextLine 'this is for testing if it read ok, will change later
End If
Loop
Close #ifile
End If
Exit Sub
err_handle:
MsgBox Err.Description
End Sub
Public Function FileExists(ByVal DirectoryAndFileName) As Boolean
On Error Resume Next
FileExists = (Len(Dir(DirectoryAndFileName, vbDirectory + vbHidden + vbSystem)) > 0)
End Function
This line:
Open (databaseFileName) For Input As #ifile
...fails, saying bad filename.
Am I on the right track?