Import text file into spreadsheet

[OTWarrior]

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:

[VBA]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
[/VBA]

This line:

[VBA] Open (databaseFileName) For Input As #ifile[/VBA]

...fails, saying bad filename.

Am I on the right track?

[will1128]

It might be a syntax issue?

The line that errors, do you need the () around the databaseFileName?

I used this 'http://support.microsoft.com/kb/272729 to learn how to open a file and parse it into Excel.

When it opens the files the syntax is:

Open FileName For Input As #FileNum

No parentheses.

Try that and see what happens.

[Tinbendr]

Also, you don't assign a value to ifile.

[vba]i = 0
ifile = FreeFile
If FileExists(databaseFileName) Then
Open databaseFileName For Input As #ifile 'Open file.
[/vba]

[OTWarrior]

Ta for that link Will, it turned out I needed this line:

[VBA]ifile = FreeFile()[/VBA]

Whoops