Macro to sum up the amout?

[tanks1308]

Hello, need your help to help me on this. You see I'm going to create
macro to sum up the staff salary in column C. Everytime i will receiv
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 macr
to export the txt file and convert in excel format? Need ur help
Thanks.

For example:

in txt file

abc 1245420
bcd 2245420

 

[Bob Phillips]

Just put this formula in D1 =SUM(C1:OFFSET(C1,,,COUNTA(C:C))), and save the
file as xls

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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.