Reading an external text file

[Ron P]

I have been asked to do some research on having excel read a text file and
from that text file extract pieces of data based on a condition and then
populate an excel sheet for anaylsis. The programmers have a text file
created as in the example and it is set up is such a manner as the required
information is in two seperate lines while the condition is in another.
example

date name number seller
amount
item
item number
sale price
value

The items being requested are:

From line 1 -- "date", "seller"
From line 2 -- "amount"
based on the "sale price" which is on line 5 of the text file

Does any one have any ideas or point me in the general direction on how I
can populate excel so that sales managers can evaluate the informtion in
excel?

The managers would like to see the excel sheet look like this

Column A Column B Column C
Date Seller Amount

Thanks and as always any assistance is appreciated.

 

[MadZebra]

Hi,

do you need an add-in to do this or are you happy to do it using a VBA macro?
the macro code should be fairly straightforward to do.

 

[Ron P]

A vba macro would work just fine. I'm pretty limited in knowledge about vba
codes

 

[MadZebra]

Just a bit more info...
1. Is the file comma delimited?
2. Are there multiple records in the file which you would like shown as
multiple rows in the spreadsheet, or just a single record to be shown in a
single row?

 

[Ron P]

The file is tab delimited. There could potentially be anywhere from 100 to
1000 records in the file set up as the example. Each occurance where the
condition is met should result in a new row in the excel sheet thus you are
correct taht there could possibly be multiple rows in the spreadsheet.


-- Ron P

 

[MadZebra]

Ok, here is the macro:

============
Sub GetFileData()
'
On Error GoTo eh_GetFileData
'
Dim filePath As String
Dim startRow As Integer
Dim startColumn As Integer
'
filePath = "D:\data.txt"
startRow = 5
startColumn = 5
'
Dim fso As Object
Set fso = CreateObject("Scripting.FilesystemObject")
'
Dim textFile As Object
Set textFile = fspenTextFile(filePath)
'
Dim fileContents() As String
fileContents = Split(textFile.ReadAll, vbCrLf)
textFile.Close
'
Dim recordCount As Integer
Dim recordPointer As Integer
recordCount = 0
For recordPointer = 0 To UBound(fileContents) Step 5
recordCount = recordCount + 1
Cells(5 + recordCount, 4) = Split(fileContents(recordPointer),
vbTab)(0)
Cells(5 + recordCount, 5) = Split(fileContents(recordPointer),
vbTab)(2)
Cells(5 + recordCount, 6) = fileContents(recordPointer + 1)
Next

CleanUp:
Set textFile = Nothing
Set fso = Nothing
Exit Sub
'
eh_GetFileData:
GoTo CleanUp
'
End Sub
===============
You will need to paste it into the code for your worksheet or workbook, then
add a button to your sheet and assign the macro name "GetFileData".
You should be able to see which bits to change for your correct file path
and sheet start position (near the beginning).
Don't forget to enable macros or of course it won't work, in fact you won't
even see the macro name when you try to assign it.
Let me know how it goes
....

 

[MadZebra]

....I just noticed a minor bug :$
Changing the startRow and startColumn values won't make any difference.
To fix this replace these lines:

Cells(5 + recordCount, 4) = Split(fileContents(recordPointer),
vbTab)(0)
Cells(5 + recordCount, 5) = Split(fileContents(recordPointer),
vbTab)(2)
Cells(5 + recordCount, 6) = fileContents(recordPointer + 1)

with these lines:

Cells(startRow + recordCount, startColumn) =
Split(fileContents(recordPointer), vbTab)(0)
Cells(startRow + recordCount, startColumn + 1) =
Split(fileContents(recordPointer), vbTab)(2)
Cells(startRow + recordCount, startColum + 2) =
fileContents(recordPointer + 1)