1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
Sub data_from_text()
'
'user pick the *.txt that has been downloaded
'
'clean the sheet
Worksheets(2).Activate
Range("a1:z100").Clear
Set mysheet = ActiveSheet
For Each CurrentChart In ActiveSheet.ChartObjects
CurrentChart.Delete
Next
Dim strTemp As String
Dim MyDataObject As DataObject
Dim i, j As Integer
Dim face As String
Dim TextPart As String
Dim FileName As Variant
'
'
' import obj file
'
'
FileName = Application.GetOpenFilename("TXT File (*.txt),*.txt,", 1, "Select a txt file to Import")
Open FileName For Binary As #1
If FileName = False Then
MsgBox ("No file was selected.")
Else
ans = MsgBox("You have selected : " & FileName _
& " Continue ?", vbOKCancel)
If ans = vbOK Then GoTo nextstep
If ans = vbCancel Then Exit Sub
End If
nextstep:
strTemp = Space$(LOF(1))
Get #1, , strTemp
strTemp = Replace(strTemp, " ", vbTab)
Set MyDataObject = New DataObject
MyDataObject.SetText strTemp
MyDataObject.PutInClipboard
Range("A1").PasteSpecial
Close #1
MyDataObject.Clear
Set MyDataObject = Nothing
mysheet.Range("A1:A65536").Find("May", LookIn:=xlValues).Select
For j = 3 To 13 Step 2
Temp = Selection.Offset(0, j).Value
Selection.Offset(0, j - 1).Value = Temp
Selection.Offset(0, j).Value = ""
Next j
'find the optimal angle
Cells.Find("deg.", LookIn:=xlValues).Select
OptAngle = Selection.Offset(0, -1).Value
'sorting table
Cells.Find("Month", LookIn:=xlValues).Select
For j = 0 To 12
Temp = ""
Temp = ActiveCell.Offset(j, 0).Value
For i = 7 To 11 Step 2
ActiveCell.Offset(j, i).Value = Temp
Next i
Next j
Cells.Find("Hh", LookIn:=xlValues).Select
ActiveCell.Value = "Irridiation Horizontal Plane"
For j = 0 To 13
Temp = ""
Temp = ActiveCell.Offset(j, 0).Value
ActiveCell.Offset(j, -1).Value = Temp
Next j
'H(angle chosen)
Cells.Find("Hopt", LookIn:=xlValues).Select
ActiveCell.Value = "Irridiation at chosen angle"
For j = 0 To 13
Temp = ""
Temp = ActiveCell.Offset(j, 2).Value
ActiveCell.Offset(j, -2).Value = Temp
ActiveCell.Offset(j, 2).Value = ""
Next j
Cells.Find("Irridiation at chosen angle", LookIn:=xlValues).Select
ActiveCell.Offset(0, -1).Value = "Irridiation at optimal angle"
For j = 0 To 13
Temp = ""
Temp = ActiveCell.Offset(j, 0).Value
ActiveCell.Offset(j, -1).Value = Temp
ActiveCell.Offset(j, 0).Value = ""
Next j
MsgBox (OptAngle)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=mysheet.Range("A6:D18"), PlotBy:=xlColumns
With ActiveChart
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "Average irridiation over a year"
.ChartArea.Height = 250
.ChartArea.Width = 500
End With
Set CO = mysheet.ChartObjects.Add(200, 400, 400, 200)
CO.Chart.ChartWizard Source:=mysheet.Range("H6:I18"), gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
HasLegend:=1
UserForm2.Show
End Sub |
