Hi,
I am very new to VBA started trying to use it Friday
I am trying to create something that will read the last five lines from all the comma delimited text files in a directory - the amount of files can vary
The files contain lists of all user that have logged into a pc for past year
date, pc name, user id,ip address
- some files only have for or 5 entries, some have hundreds
I only need the last five ideally in reverse order - latest entry first
I have a bit of code i'm using that gives me all the entries in all the files which I found in the net
I then have some code that reads a single comma sepaerated text file with no file extension into another table
This is an archived document containing user details for each user id
I then perform a join on the id to give me full details
It works ok but I would really like reduce the number of results to 5 entries per PC
I cant include the files because they are confidential
The code I have for reading the files in is
Sub getallfiles()
Dim f As Object '//File Object
With CreateObject("Scripting.FileSystemObject")
For Each f In .GetFolder("C:\Users\Ernie\Desktop\work\we2").Files
DoCmd.TransferText , , "MyTable", f.Path, False 'import file
DoEvents 'don't take over all of the PC resources
Next f
End With
CurrentDb.TableDefs("MyTable").Fields("F1").Name = "Date"
CurrentDb.TableDefs("MyTable").Fields("F2").Name = "PC1"
CurrentDb.TableDefs("MyTable").Fields("F3").Name = "User"
CurrentDb.TableDefs("MyTable").Fields("F4").Name = "IP"
End Sub
The path is my local pc but for real use will be a folder on a network drive
I've seen code to read the last five lines by reading the lines into an array and iterating through them backwards but dont know how to incorperate them into the code that works so far
Is there a way of making this code do what I want or a better way of achiving the whole thing
lots of comments would be a help too I would like to understand the code to help me learn
Thanks in advance
Ernie
I am very new to VBA started trying to use it Friday
I am trying to create something that will read the last five lines from all the comma delimited text files in a directory - the amount of files can vary
The files contain lists of all user that have logged into a pc for past year
date, pc name, user id,ip address
- some files only have for or 5 entries, some have hundreds
I only need the last five ideally in reverse order - latest entry first
I have a bit of code i'm using that gives me all the entries in all the files which I found in the net
I then have some code that reads a single comma sepaerated text file with no file extension into another table
This is an archived document containing user details for each user id
I then perform a join on the id to give me full details
It works ok but I would really like reduce the number of results to 5 entries per PC
I cant include the files because they are confidential
The code I have for reading the files in is
Sub getallfiles()
Dim f As Object '//File Object
With CreateObject("Scripting.FileSystemObject")
For Each f In .GetFolder("C:\Users\Ernie\Desktop\work\we2").Files
DoCmd.TransferText , , "MyTable", f.Path, False 'import file
DoEvents 'don't take over all of the PC resources
Next f
End With
CurrentDb.TableDefs("MyTable").Fields("F1").Name = "Date"
CurrentDb.TableDefs("MyTable").Fields("F2").Name = "PC1"
CurrentDb.TableDefs("MyTable").Fields("F3").Name = "User"
CurrentDb.TableDefs("MyTable").Fields("F4").Name = "IP"
End Sub
The path is my local pc but for real use will be a folder on a network drive
I've seen code to read the last five lines by reading the lines into an array and iterating through them backwards but dont know how to incorperate them into the code that works so far
Is there a way of making this code do what I want or a better way of achiving the whole thing
lots of comments would be a help too I would like to understand the code to help me learn
Thanks in advance
Ernie