to get the code to open the file and parse it into columns properly, turn on
the macro recorder (tools=Macro=Record a New Macro), then manually open
and parse the file with the text import wizard

File=Open

select fixed rather than delimited and set up your columns on the next
dialog.

When you are done, turn off the macro recorder.

then you will have recorded the OpenText method with the appropriate
arguments

Now look at the code

You should have

Workbooks.OpenText Filename:="C:\MyTextfiles\File1.txt", . . .

You can substitute the hard coded filename with something like

Dim sStr as String
sStr = "C:\MyTextFiles\File1.txt"

Workbooks.OpenText Filename:=sStr, . . .

or you can show the File Open dialog so when you run your macro, you select
the file to open

Dim sStr as String
sStr = Application.GetOpenFilename( "Text Files (*.txt),*.txt")
if not sStr = "False" then " user hit cancel if it returns false)
Workbooks.OpenText Filename:=sStr, . . .
End if


when you run the macro, it won't show the text import wizard - it will just
use the settings that you selected and it recorded.

--
Regards,
Tom Ogilvy



"tanks1308 " wrote in message
...
Hello, need your help to help me on this. You see I'm going to create a
macro to sum up the staff salary in column C. Everytime i will receive
a data file in txt format where the file will have a number of staff.
Every month the number of staff will change. So how can I use the macro
to export the txt file and convert in excel format? Need ur help.
Thanks.

For example:

in txt file

abc 1245420
bcd 2245420