Read 1st line of text files

[KGee]

I want to read the first line from two different report files and assign the dates from each file to separate variables in Excel. They are both comma delimited text files and the dates are enclosed in double-quotes. The dates are the 2nd and 3rd fields in the first line of the files as shown below (the "P" is not needed and can be ignored).

"P","02/06/2011","02/19/2011"

The two file names and folder location will not change so I can hard code those values as the files are overwritten each time a new report file is created. There will always be two dates formatted in MM/DD/YYYY format. I want to assign each of the dates to separate variables for use in my macro, something like: file1a, file1b, file2a, file2b. I tried looking at the Open statement and OpenTextFile method but need some help.

 

[Ruddles]

Perhaps something along these lines:-

Option Explicit
 
Dim File1a As Date
Dim File1b As Date
Dim File2a As Date
Dim File2b As Date
 
Public Sub ReadFiles()
 
  ExtractDate "[COLOR=red][B]c:\temp\file1.txt[/B][/COLOR]", File1a, File1b
  ExtractDate "[COLOR=red][B]c:\temp\file2.txt[/B][/COLOR]", File2a, File2b
 
End Sub
 
Private Sub ExtractDate(ByVal sFile As String, ByRef dVar1 As Date, dVar2 As Date)
 
  Dim intFH As Integer
  Dim sRec As String
  Dim sStr As String
  
  Close
  intFH = FreeFile()
  Open sFile For Input As #intFH
  Input #intFH, sRec, sStr
  dVar1 = DateValue(sStr)
  Input #intFH, sStr
  dVar2 = DateValue(sStr)
  Close

 
End Sub

 

[KGee]

OK, that works but I don't get how. I stepped through the code and can see what's happening but I don't understand the lines in red.

Open sFile For Input As #intFH
[COLOR=red][B]Input #intFH, sRec, sStr[/B][/COLOR]
dVar1 = DateValue(sStr)
[COLOR=red][B]Input #intFH, sStr[/B][/COLOR]
dVar2 = DateValue(sStr)
Close

I then experimented with the code and saw I could also do this:

Open sFile For Input As #intFH
Input #intFH, sRec, sStr, [B][COLOR=red]sStr2[/COLOR][/B]
dVar1 = DateValue(sStr)
[COLOR=darkgreen]'Input #intFH, sStr[/COLOR]
dVar2 = DateValue([B][COLOR=red]sStr2[/COLOR][/B])
Close

How does it know the file is delimited and what delimiter was used? And why does it only read the first line (that's what I want, just curious)?

Thank you.

 

[Ruddles]

My code reads two items from the file, putting the "P" into sRec and the first date into sStr (which it then puts into dVar1), then it reads one more item and puts that into sStr (which it then puts into dVar2). I do it in two statements, you do it in one, but the result is the same.

Input# knows that data items will be comma-separated, so each time you do an Input# it fetches as many pieces of data from the file as there are variables to put them in.

Only the first line was read because there were only three 'data fetch' operations in total: I did them in two commands, you did them in one.

Note: sometimes you want/need/prefer to read a complete line in, regardless of commas in which case you would do Line Input #intFH, sRec and then you'd end up with "P","02/06/2011","02/19/2011" in sRec, complete with quotes and commas. You could then process the line as a whole or break it up into an array using the Split command.

 

[KGee]

I understand now. I read your explanation and have a better understanding after experimenting further with the code. It would have helped if I read the description in help file as opposed to just looking at the example they provided.

Thanks!