VBA to extract data from a .txt file

[nevillestoke]

I have looked through the site but can't find anything quite like this - I know a little VBA, but this seems to be more than I imagined. I have a .txt file that changes monthly in one column - the Gross Pay - and I would like to extract the Gross Pay from the .txt file (let's say the file is named "2011monthly.txt") based on the code which may be one, two or three characters and is "joined" to the name. I am comfortable enough with the Vlookup function, but not when extracting from a text file. Also, the list of names is quite stable with only an occasional addition or deletion.

I am using Excel 2003 on Windows XP Professional and an example of how the data is presented is given below.

11-02-22 2011XYZ Company
03:07 123 Any Street
Toronto, ON

CURRENT MONTH

Period/Year: 05/11 to 08/11
Employees: ALL
Departments: ALL
StatusTypes: ALL
Pay Types: ALL

Code Name Dept Gross Pay
------------------------------- ---------------
1 Doe, John PLANT 1500
2 Public, Jane P. PLANT 1650
55 Jones, J.J. DRIVER 2150
75 Smith, Kevin PLANT 1350
102 Simpson, Homer Office 1158.66
175 Ball, Lucille Office 1291.6

I would appreciate any help you can offer and can provided additional information if needed.

Thanks, Neville.

 

[dantheman9]

HI Neville,

As a rough fix, why not import the lot into a temp Sheet then copy the column that you need from it?

(or even set the import to just get the Gross PAy column;

With NewSht.QueryTables.Add( _
Connection:="TEXT;" & FILENAME _
, Destination:=NewSht.Range("$A$1"))
blah blah other settings

.TextFileColumnDataTypes = Array(9, 9, 9, 1,)