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 |
Partager