D1wolf
03-11-2015, 09:11 AM
Hello experts.
trying to copy a weeks worth of data from txt file to excel2007. Maybe experts could offer alternatives.
i first ask user for starting date and am expecting about 200 - 330 rows depending on txt file.
i have other macro opening txt files, normally very large- months of data.
Sub Macro3()
'
' Macro3 Macro
'
'
Dim Date1 As String
Date1 = InputBox("Enter Date to begin capture x/y/zzzz ", title)
Windows("12264MISA.TXT").Activate
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Range("B:B").Select
Selection.Find(What:=Date1, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Range("A1:B1").Select
ActiveCell.Activate
ActiveCell.EntireRow.Select
MsgBox "1 Now at " & ActiveCell.Address 'ok here
SendKeys ("+{DOWN 200}"), True ' get 1weeks worth
MsgBox "2 Now at " & ActiveCell.Address 'not ok
Selection.Copy
Windows("Heath-Report-Data.xls").Activate
Sheets("data-pm4").Select
Range("A2").Select ' Maintain column headers
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("form").Select
End Sub
works for 1 row(different spreadsheet)
Time
Date
SYSTEM NUMBER
GRADE
REEL NUMBER
AVG SPEED
AVG WIDTH
MOI AVG TGT
MOI AVG ACT
MOI TOT SPREAD
MOI CD SPREAD
MOI MD SPREAD
MOI PERCENT MEASURED
MOI PERCENT MD CONTROL
BW AVG TGT
BW AVG ACT
BW TOT SPREAD
BW CD SPREAD
BW MD SPREAD
BW PERCENT MEASURED
BW PERCENT MD CONTROL
RUN TIME
ELAPSED
PRODUCTION
REEL DW CALC SP
REEL DW CALC PV
0:00:33
1/1/2015
8493
1009
28
2305.661
91.76322
6
6.022001
2.439778
0.29452
91.734214
100
11.47
11.45334
0.642221
0.16285
91.734214
100
1:13:38
1/1/2012
8493
2
2415.951
91.41666
5.9
5.900173
2.282197
1.959131
0.454008
100
100
12.01
12.01626
0.973659
0.755378
0.235794
100
100
100
2392.906
2595.906
1.476406
11.93505
11.86981
eg of data
18:26:07
2/22/2015
12264
1097
4563.679
7.4
7.38276
1.324862
0.316169
84.71858
83.24797
22.1
22.09597
0.983628
0.283755
84.71858
83.24797
21.6
20.46292
23
23.01229
2.133525
0.495408
100
100
100
19:07:35
2/22/2015
12264
1097
4563.664
7.4
7.374635
1.325568
0.237211
85.60988
84.2046
22.1
22.10499
0.985166
0.201847
85.60988
84.2046
21.6
20.47349
23
23.00056
2.134028
0.526185
100
100
100
19:48:39
2/22/2015
12264
1097
4563.681
7.4
7.426607
1.367794
0.327656
86.4838
85.06458
22.1
22.09214
0.996608
0.300478
86.4838
85.06458
21.6
20.44955
23
23.06534
2.124124
0.565892
100
100
100
20:29:39
2/22/2015
12264
1097
4563.672
7.4
7.401958
1.337754
0.259882
84.91568
83.44932
22.1
22.11194
0.985259
0.207457
84.91568
83.44932
21.6
20.4738
23
22.96714
2.097773
0.454588
100
100
100
21:22:37
2/22/2015
12264
1297
4563.614
7.4
7.311519
1.330961
0.424791
71.88948
68.98735
22.1
22.10876
0.998317
0.25021
71.88948
68.98735
21.6
20.49114
23
22.86969
2.131745
0.7853
86.08496
83.30089
87.11808
22:03:37
2/22/2015
12264
1297
4563.672
7.4
7.390338
1.408526
0.388825
69.21549
67.79302
22.1
22.09368
1.00524
0.227194
69.21549
67.79302
21.6
20.45885
22.83828
22.74891
1.985395
1.28343
82.08277
79.23322
83.18275
trying to copy a weeks worth of data from txt file to excel2007. Maybe experts could offer alternatives.
i first ask user for starting date and am expecting about 200 - 330 rows depending on txt file.
i have other macro opening txt files, normally very large- months of data.
Sub Macro3()
'
' Macro3 Macro
'
'
Dim Date1 As String
Date1 = InputBox("Enter Date to begin capture x/y/zzzz ", title)
Windows("12264MISA.TXT").Activate
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Range("B:B").Select
Selection.Find(What:=Date1, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Range("A1:B1").Select
ActiveCell.Activate
ActiveCell.EntireRow.Select
MsgBox "1 Now at " & ActiveCell.Address 'ok here
SendKeys ("+{DOWN 200}"), True ' get 1weeks worth
MsgBox "2 Now at " & ActiveCell.Address 'not ok
Selection.Copy
Windows("Heath-Report-Data.xls").Activate
Sheets("data-pm4").Select
Range("A2").Select ' Maintain column headers
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("form").Select
End Sub
works for 1 row(different spreadsheet)
Time
Date
SYSTEM NUMBER
GRADE
REEL NUMBER
AVG SPEED
AVG WIDTH
MOI AVG TGT
MOI AVG ACT
MOI TOT SPREAD
MOI CD SPREAD
MOI MD SPREAD
MOI PERCENT MEASURED
MOI PERCENT MD CONTROL
BW AVG TGT
BW AVG ACT
BW TOT SPREAD
BW CD SPREAD
BW MD SPREAD
BW PERCENT MEASURED
BW PERCENT MD CONTROL
RUN TIME
ELAPSED
PRODUCTION
REEL DW CALC SP
REEL DW CALC PV
0:00:33
1/1/2015
8493
1009
28
2305.661
91.76322
6
6.022001
2.439778
0.29452
91.734214
100
11.47
11.45334
0.642221
0.16285
91.734214
100
1:13:38
1/1/2012
8493
2
2415.951
91.41666
5.9
5.900173
2.282197
1.959131
0.454008
100
100
12.01
12.01626
0.973659
0.755378
0.235794
100
100
100
2392.906
2595.906
1.476406
11.93505
11.86981
eg of data
18:26:07
2/22/2015
12264
1097
4563.679
7.4
7.38276
1.324862
0.316169
84.71858
83.24797
22.1
22.09597
0.983628
0.283755
84.71858
83.24797
21.6
20.46292
23
23.01229
2.133525
0.495408
100
100
100
19:07:35
2/22/2015
12264
1097
4563.664
7.4
7.374635
1.325568
0.237211
85.60988
84.2046
22.1
22.10499
0.985166
0.201847
85.60988
84.2046
21.6
20.47349
23
23.00056
2.134028
0.526185
100
100
100
19:48:39
2/22/2015
12264
1097
4563.681
7.4
7.426607
1.367794
0.327656
86.4838
85.06458
22.1
22.09214
0.996608
0.300478
86.4838
85.06458
21.6
20.44955
23
23.06534
2.124124
0.565892
100
100
100
20:29:39
2/22/2015
12264
1097
4563.672
7.4
7.401958
1.337754
0.259882
84.91568
83.44932
22.1
22.11194
0.985259
0.207457
84.91568
83.44932
21.6
20.4738
23
22.96714
2.097773
0.454588
100
100
100
21:22:37
2/22/2015
12264
1297
4563.614
7.4
7.311519
1.330961
0.424791
71.88948
68.98735
22.1
22.10876
0.998317
0.25021
71.88948
68.98735
21.6
20.49114
23
22.86969
2.131745
0.7853
86.08496
83.30089
87.11808
22:03:37
2/22/2015
12264
1297
4563.672
7.4
7.390338
1.408526
0.388825
69.21549
67.79302
22.1
22.09368
1.00524
0.227194
69.21549
67.79302
21.6
20.45885
22.83828
22.74891
1.985395
1.28343
82.08277
79.23322
83.18275