VBA Excel macros

[issacraj]

i need to extract a report from an ASCII file i have partially succeeded in getting that report... in it I have a field for time and a field for no of products...i have to separate the no.of products done in regular time and no of products done in overtime say after 5:30 and put them in two separate columns... how to accomplish this... also when loading the report i get data two times that is the columns get replicated twice....any suggestions please... i need them ASAP

[Teeroy]

Can you load a sample of the text file to show the format?

[issacraj]

hi,

thanks for the reply... i have stopped redundant data
the sample ASCII file is like this

2012-07-13; 212; 4001; 06:50:51; 0; 200; 8; 4014; ; 12; 8525; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 212; 4001; 06:50:51; 0; 200; 8; 4420; ; 4; 8224; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 212; 4001; 11:30:00; 0; 0; 0; 0; ; 0; 0; 0.000; 0.000; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 212; 4001; 14:12:05; 0; 200; 8; 4015; ; 2; 2579; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 212; 4001; 14:12:05; 0; 200; 8; 4420; ; 18; 9296; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 212; 4001; 17:30:00; 0; 0; 0; 0; ; 0; 0; 0.000; 0.000; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 616; 5023; 08:36:20; 0; 0; 0; 0; ; 0; 321; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 616; 5023; 08:41:41; 0; 0; 0; 0; ; 0; 0; 0.000; 0.000; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 3008; 4033; 07:27:50; 0; 0; 0; 0; ; 0; 4130; 0.000; 0.000; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0;
2012-07-13; 3008; 4033; 07:27:50; 0; 0; 0; 16194601; 0715 ; 1; 16; 0.000; 0.000; 1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0;

I have managed to get them as


Date Emp No Station No Time Opertion Code Job Order Products Overtime Regular time
7/8/2012 212 4001 6:46:39 200 8 4014 8 104 0 1
7/8/2012 212 4001 6:46:39 200 8 4015 2 3658
7/8/2012 212 4001 6:46:39 200 8 4016 1 1152
7/8/2012 212 4001 6:46:39 200 8 4420 27 12087
7/8/2012 212 4001 11:30:00 0 0 0 0 0

i used the formula to get overtime regular time as =IF(E4>TIMEVALUE("5:30:00 pm"),"1","0")

=IF(E4< TIMEVALUE("5:30:00 pm"),"1","0")

I NEED TO AFFECT ALL THE ROWS WITH THIS FORMULA ...not one row alone as i have done...how to do this any ideas

thanks in advance

[Teeroy]

Hi issacraj,

The following will put the formula in each row in Column I . I'm not sure I got the columns you want the formula in right but that's easy enough for you to change. Since you have headers you start looping from row 2. The important thing to remember is to get " written in the formula you need it as double ("") otherwise VBA thinks you are just assembling a string.


Sub insertFormula()
Dim i As Long

For i = 2 To ActiveSheet.UsedRange.Rows.Count
Range("I" & i).Formula = "=IF(E" & i & "< TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
Next i
End Sub

[issacraj]

thanks for the code Teeroy

[issacraj]

Teeroy... i am loading a ASCII file called transfer.asc to get my report... i do it statically... but i want the user to select to select whatever ascii file in whatever pathname... i use this code

With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\transfer.Asc", _
Destination:=Range("A4"))

but i want the pathname to be assigned dynamically as the user selects his file... i think getfile function can be used..but i dont know how...any ideas

thanks in advance...

[Teeroy]

I haven't used a query table for this purpose but if your code worked for you this extension should work. To be clear the following ISN'T tested.


FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Ascii Files (*.asc),*.asc")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileToOpen , _
Destination:=Range("A4"))

[issacraj]

thank you teeroy... it was wonderful