PDA

View Full Version : Import text file into spreadsheet



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?

will1128
01-21-2011, 11:18 AM
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
01-21-2011, 12:40 PM
Also, you don't assign a value to ifile.

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

OTWarrior
01-24-2011, 03:00 AM
Ta for that link Will, it turned out I needed this line:

ifile = FreeFile()

Whoops :)