Ago
05-07-2010, 10:22 AM
I have a add-in macro file that runs when i open a csv-file.
The macro removes useless data (and some other stuff), but now i have found a use for that useless data.
But i need that data split in three files.
Is there any way that i can use something like Range(rng).SaveAs ?
Lets call them rng1, rng2 and rng3.
Can i save rng1 as a new csv file called airplane.csv
rng2 as freefall.csv
and rng3 as canopy.csv?
All ranges are ~50-75 rows and two columns if that makes any difference.
Can this be done without having to open a new document, copy paste, save, and close three times?
The main reason is the time and that it will be lots of "funny stuff" happening on screen.
I post the code i have here if its of any help:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
Dim A As Double
Dim wkbName As String
wkbName = Application.Workbooks(1).Name
extension = Mid(wkbName, InStr(wkbName, "."))
If extension = ".csv" And Range("A1").Value = "LATITUDE" And Range("B1").Value = "LONGITUDE" _
And Range("C1").Value = "ALTITUDE" And Range("D1").Value = "SPEED" Then
Response = MsgBox(prompt:="Run GPS-Script?", Buttons:=vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Else
Exit Sub
End If
Columns(1).Insert
Rows(2).Insert
Rows(2).Insert
Rows(2).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B5", "E" & LastRow).NumberFormat = "@"
For Each Dcell In Range("B5", "E" & LastRow)
A = Replace(Dcell.Value, ".", ",")
Dcell.Value = A
Next
Columns(4).Insert
Range("D:D").NumberFormat = "0"
Range("D6").Value = "=ACOS(COS(RADIANS(90-B5)) *COS(RADIANS(90-B6)) _
+SIN(RADIANS(90-B5)) *SIN(RADIANS(90-B6)) *COS(RADIANS(C5-C6))) *6371000"
Range("D7").Value = "=D6+ACOS(COS(RADIANS(90-B6)) *COS(RADIANS(90-B7)) _
+SIN(RADIANS(90-B6)) *SIN(RADIANS(90-B7)) *COS(RADIANS(C6-C7))) *6371000"
Range("D7", "D" & LastRow).FillDown
Columns(6).Insert
Range("E:G").NumberFormat = "0"
Range("F6").Value = "=((E5-E6)/1000)*60*60"
Range("F6", "F" & LastRow).FillDown
Columns(8).Insert
Range("H:H").NumberFormat = "0"
Range("H6").Value = "=SQRT(F6*F6+G6*G6)"
Range("H6", "H" & LastRow).FillDown
Columns(9).Insert
Range("I:I").NumberFormat = "0.000"
Range("I6").Value = "=G6/F6"
Range("I6", "I" & LastRow).FillDown
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
Other:=True, OtherChar:="Z"
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
Other:=True, OtherChar:="T"
Range("A1", "L1").ClearContents
Range("B1").Value = "Latitude"
Range("C1").Value = "Longitude"
Range("D1").Value = "H-Distance"
Range("E1").Value = "Altitude"
Range("F1").Value = "V-Speed"
Range("G1").Value = "H-Speed"
Range("H1").Value = "3D-Speed"
Range("I1").Value = "Glide"
Range("J1").Value = "Date"
Range("K1").Value = "Time"
Range("A2").Value = "Max"
Range("A3").Value = "Min"
Range("A4").Value = "Avg"
For i = 120 To LastRow
If Range("F" & i).Value < 100 And Range("E" & i).Value < 2000 Then
Exit For
End If
If Range("F" & i).Value > 100 And (Minute(Range("K" & i).Value) - Minute(Range("K" & i - 1).Value)) * 60 _
+ Second(Range("K" & i).Value) - Second(Range("K" & i - 1).Value) < 2 And j = "" Then
j = i
End If
If Range("E" & i).Value < 1400 And FreefallEnd = "" Then
FreefallEnd = i
End If
If Range("F" & i).Value > 160 And Range("F" & i).Value < 300 And Range("F" & i + 1).Value > 160 _
And FreefallStart = "" Then
FreefallStart = i
End If
Next i
Range(Cells(i + 8, 1), Cells(LastRow, 1)).EntireRow.Delete
Range(Cells(5, 1), Cells(j - 11, 1)).EntireRow.Delete
FreefallEnd = FreefallEnd - j + 15
FreefallStart = FreefallStart - j + 15
Rows("5:5").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
Cells.Columns.AutoFit
Range("F5").ClearContents
Range("H5", "I5").ClearContents
Range("D5", "D9").Value = "0"
Range("D2").Value = Range("D" & FreefallEnd).Value
Range("F2").Value = "=MAX(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("F3").Value = "=MIN(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("F4").Value = "=AVERAGE(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("G2").Value = "=MAX(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("G3").Value = "=MIN(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("G4").Value = "=AVERAGE(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("H2").Value = "=MAX(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("H3").Value = "=MIN(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("H4").Value = "=AVERAGE(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("I2").Value = "=MAX(I" & FreefallStart & ":I" & FreefallEnd & ")"
Range("I3").Value = "=MIN(I" & FreefallStart & ":I" & FreefallEnd & ")"
Range("I4").Value = "=AVERAGE(I" & FreefallStart & ":I" & FreefallEnd & ")"
End Sub
' Initiation of App object
Private Sub Workbook_Open()
Set App = Application
End Sub
The macro removes useless data (and some other stuff), but now i have found a use for that useless data.
But i need that data split in three files.
Is there any way that i can use something like Range(rng).SaveAs ?
Lets call them rng1, rng2 and rng3.
Can i save rng1 as a new csv file called airplane.csv
rng2 as freefall.csv
and rng3 as canopy.csv?
All ranges are ~50-75 rows and two columns if that makes any difference.
Can this be done without having to open a new document, copy paste, save, and close three times?
The main reason is the time and that it will be lots of "funny stuff" happening on screen.
I post the code i have here if its of any help:
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
Dim A As Double
Dim wkbName As String
wkbName = Application.Workbooks(1).Name
extension = Mid(wkbName, InStr(wkbName, "."))
If extension = ".csv" And Range("A1").Value = "LATITUDE" And Range("B1").Value = "LONGITUDE" _
And Range("C1").Value = "ALTITUDE" And Range("D1").Value = "SPEED" Then
Response = MsgBox(prompt:="Run GPS-Script?", Buttons:=vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Else
Exit Sub
End If
Columns(1).Insert
Rows(2).Insert
Rows(2).Insert
Rows(2).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B5", "E" & LastRow).NumberFormat = "@"
For Each Dcell In Range("B5", "E" & LastRow)
A = Replace(Dcell.Value, ".", ",")
Dcell.Value = A
Next
Columns(4).Insert
Range("D:D").NumberFormat = "0"
Range("D6").Value = "=ACOS(COS(RADIANS(90-B5)) *COS(RADIANS(90-B6)) _
+SIN(RADIANS(90-B5)) *SIN(RADIANS(90-B6)) *COS(RADIANS(C5-C6))) *6371000"
Range("D7").Value = "=D6+ACOS(COS(RADIANS(90-B6)) *COS(RADIANS(90-B7)) _
+SIN(RADIANS(90-B6)) *SIN(RADIANS(90-B7)) *COS(RADIANS(C6-C7))) *6371000"
Range("D7", "D" & LastRow).FillDown
Columns(6).Insert
Range("E:G").NumberFormat = "0"
Range("F6").Value = "=((E5-E6)/1000)*60*60"
Range("F6", "F" & LastRow).FillDown
Columns(8).Insert
Range("H:H").NumberFormat = "0"
Range("H6").Value = "=SQRT(F6*F6+G6*G6)"
Range("H6", "H" & LastRow).FillDown
Columns(9).Insert
Range("I:I").NumberFormat = "0.000"
Range("I6").Value = "=G6/F6"
Range("I6", "I" & LastRow).FillDown
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
Other:=True, OtherChar:="Z"
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
Other:=True, OtherChar:="T"
Range("A1", "L1").ClearContents
Range("B1").Value = "Latitude"
Range("C1").Value = "Longitude"
Range("D1").Value = "H-Distance"
Range("E1").Value = "Altitude"
Range("F1").Value = "V-Speed"
Range("G1").Value = "H-Speed"
Range("H1").Value = "3D-Speed"
Range("I1").Value = "Glide"
Range("J1").Value = "Date"
Range("K1").Value = "Time"
Range("A2").Value = "Max"
Range("A3").Value = "Min"
Range("A4").Value = "Avg"
For i = 120 To LastRow
If Range("F" & i).Value < 100 And Range("E" & i).Value < 2000 Then
Exit For
End If
If Range("F" & i).Value > 100 And (Minute(Range("K" & i).Value) - Minute(Range("K" & i - 1).Value)) * 60 _
+ Second(Range("K" & i).Value) - Second(Range("K" & i - 1).Value) < 2 And j = "" Then
j = i
End If
If Range("E" & i).Value < 1400 And FreefallEnd = "" Then
FreefallEnd = i
End If
If Range("F" & i).Value > 160 And Range("F" & i).Value < 300 And Range("F" & i + 1).Value > 160 _
And FreefallStart = "" Then
FreefallStart = i
End If
Next i
Range(Cells(i + 8, 1), Cells(LastRow, 1)).EntireRow.Delete
Range(Cells(5, 1), Cells(j - 11, 1)).EntireRow.Delete
FreefallEnd = FreefallEnd - j + 15
FreefallStart = FreefallStart - j + 15
Rows("5:5").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
Cells.Columns.AutoFit
Range("F5").ClearContents
Range("H5", "I5").ClearContents
Range("D5", "D9").Value = "0"
Range("D2").Value = Range("D" & FreefallEnd).Value
Range("F2").Value = "=MAX(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("F3").Value = "=MIN(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("F4").Value = "=AVERAGE(F" & FreefallStart & ":F" & FreefallEnd & ")"
Range("G2").Value = "=MAX(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("G3").Value = "=MIN(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("G4").Value = "=AVERAGE(G" & FreefallStart & ":G" & FreefallEnd & ")"
Range("H2").Value = "=MAX(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("H3").Value = "=MIN(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("H4").Value = "=AVERAGE(H" & FreefallStart & ":H" & FreefallEnd & ")"
Range("I2").Value = "=MAX(I" & FreefallStart & ":I" & FreefallEnd & ")"
Range("I3").Value = "=MIN(I" & FreefallStart & ":I" & FreefallEnd & ")"
Range("I4").Value = "=AVERAGE(I" & FreefallStart & ":I" & FreefallEnd & ")"
End Sub
' Initiation of App object
Private Sub Workbook_Open()
Set App = Application
End Sub