Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to sum up the amout?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to sum up the amout?
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) "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to sum up the amout?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
calculate loan amout | Excel Worksheet Functions | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |