Ago
10-21-2010, 10:10 AM
Im trying to create a Excel add-in that calculates a bunch of numbers from a GPS in a csv-file and then creates a set of charts of the data.
The code that calculates the data works, and the chart-thing works IF its in a module.
The problem comes when i add it to my add in file.
The code should create a new sheet where it adds the chart, but because its a add in the code creats the new sheet on the xlam-file instead of the csv file.
I tried to activate the workbook in every way i can think of but it dont seem to work.
Any suggestions? Anything i can change to make it work?
Below the line is where the problems start.
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
Dim wkbName As String
Dim LastRow As Double
wkbName = Application.Workbooks(1).Name
extension = Mid(wkbName, InStr(wkbName, "."))
shtName = Left(wkbName, InStr(wkbName, ".") - 1)
If extension = ".CSV" And Range("A1").Value = "time,lat,lon,hMSL,velN,velE,velD,hAcc,vAcc,sAcc,gpsFix,numSV" Then
Response = MsgBox(prompt:="Run GPS-Script?", Buttons:=vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Else
Exit Sub
End If
'Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").FormulaR1C1 = "velH"
Range("G2").FormulaR1C1 = "(km/h)"
Range("G3").FormulaR1C1 = "=SQRT(RC[-2]*RC[-2]+RC[-1]*RC[-1])*3.6"
Range("G3:G" & LastRow).FillDown
Range("G3:G" & LastRow).NumberFormat = "0.00"
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").FormulaR1C1 = "velD"
Range("I2").FormulaR1C1 = "(km/h)"
Range("I3").FormulaR1C1 = "=RC[-1]*3.6"
Range("I3:I" & LastRow).FillDown
Range("I3:I" & LastRow).NumberFormat = "0.00"
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").FormulaR1C1 = "Glide"
Range("J3").FormulaR1C1 = "=RC[-3]/RC[-1]"
Range("J3:J" & LastRow).FillDown
Range("J3:J" & LastRow).NumberFormat = "0.00"
Rows("3:3").Select
ActiveWindow.FreezePanes = True
Columns("I:I").Select
Selection.Font.Bold = True
Columns("G:G").Select
Selection.Font.Bold = True
Columns("D:D").Select
Selection.Font.Bold = True
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.Font.Bold = True
Selection.NumberFormat = "0.000"
Range("A1").Select
________________________________________________________________
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""velH"""
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""velD"""
ActiveChart.SeriesCollection(2).Values = "='" & shtName & "'!$I$3:$I$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""hMSL"""
ActiveChart.SeriesCollection(3).Values = "='" & shtName & "'!$D$3:$D$" & LastRow
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).AxisGroup = 2
Set Rng = ActiveSheet.Range("A1:R27")
With ActiveChart.Parent
.Left = Rng.Left
.Width = Rng.Width
.Top = Rng.Top
.Height = Rng.Height
End With
ActiveSheet.Name = "velH-velD-hMSL"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""velH"""
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""velD"""
ActiveChart.SeriesCollection(2).Values = "='" & shtName & "'!$I$3:$I$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""Glide"""
ActiveChart.SeriesCollection(3).Values = "='" & shtName & "'!$J$3:$J$" & LastRow
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).AxisGroup = 2
Set Rng = ActiveSheet.Range("A1:R27")
With ActiveChart.Parent
.Left = Rng.Left
.Width = Rng.Width
.Top = Rng.Top
.Height = Rng.Height
End With
ActiveSheet.Name = "velH-velD-Glide"
'Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Open()
Set App = Application
End Sub
Attaching the xlam-file and a example CSV-file.
Installation Excel 2007
1. Download file.
2. Place xlam-file in your AddIns folder. C:\Users\ <USERNAME> \AppData\Roaming\Microsoft\AddIns
3. Open Excel
4. Click on the Excel "main button" in top left corner and choose |Excel options|.
5. Click on Add-Ins on the left.
6. At the bottom of the window it says Manage <Scrollbar> |Go|, choose Excel Add-Ins and press Go.
7. A window with avalible Add-Ins should pop up, mark Flysight and press |Ok|.
8. Close Excel and open the CSV file.
The code that calculates the data works, and the chart-thing works IF its in a module.
The problem comes when i add it to my add in file.
The code should create a new sheet where it adds the chart, but because its a add in the code creats the new sheet on the xlam-file instead of the csv file.
I tried to activate the workbook in every way i can think of but it dont seem to work.
Any suggestions? Anything i can change to make it work?
Below the line is where the problems start.
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
Dim wkbName As String
Dim LastRow As Double
wkbName = Application.Workbooks(1).Name
extension = Mid(wkbName, InStr(wkbName, "."))
shtName = Left(wkbName, InStr(wkbName, ".") - 1)
If extension = ".CSV" And Range("A1").Value = "time,lat,lon,hMSL,velN,velE,velD,hAcc,vAcc,sAcc,gpsFix,numSV" Then
Response = MsgBox(prompt:="Run GPS-Script?", Buttons:=vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Else
Exit Sub
End If
'Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").FormulaR1C1 = "velH"
Range("G2").FormulaR1C1 = "(km/h)"
Range("G3").FormulaR1C1 = "=SQRT(RC[-2]*RC[-2]+RC[-1]*RC[-1])*3.6"
Range("G3:G" & LastRow).FillDown
Range("G3:G" & LastRow).NumberFormat = "0.00"
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").FormulaR1C1 = "velD"
Range("I2").FormulaR1C1 = "(km/h)"
Range("I3").FormulaR1C1 = "=RC[-1]*3.6"
Range("I3:I" & LastRow).FillDown
Range("I3:I" & LastRow).NumberFormat = "0.00"
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").FormulaR1C1 = "Glide"
Range("J3").FormulaR1C1 = "=RC[-3]/RC[-1]"
Range("J3:J" & LastRow).FillDown
Range("J3:J" & LastRow).NumberFormat = "0.00"
Rows("3:3").Select
ActiveWindow.FreezePanes = True
Columns("I:I").Select
Selection.Font.Bold = True
Columns("G:G").Select
Selection.Font.Bold = True
Columns("D:D").Select
Selection.Font.Bold = True
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.Font.Bold = True
Selection.NumberFormat = "0.000"
Range("A1").Select
________________________________________________________________
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""velH"""
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""velD"""
ActiveChart.SeriesCollection(2).Values = "='" & shtName & "'!$I$3:$I$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""hMSL"""
ActiveChart.SeriesCollection(3).Values = "='" & shtName & "'!$D$3:$D$" & LastRow
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).AxisGroup = 2
Set Rng = ActiveSheet.Range("A1:R27")
With ActiveChart.Parent
.Left = Rng.Left
.Width = Rng.Width
.Top = Rng.Top
.Height = Rng.Height
End With
ActiveSheet.Name = "velH-velD-hMSL"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""velH"""
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""velD"""
ActiveChart.SeriesCollection(2).Values = "='" & shtName & "'!$I$3:$I$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""Glide"""
ActiveChart.SeriesCollection(3).Values = "='" & shtName & "'!$J$3:$J$" & LastRow
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).AxisGroup = 2
Set Rng = ActiveSheet.Range("A1:R27")
With ActiveChart.Parent
.Left = Rng.Left
.Width = Rng.Width
.Top = Rng.Top
.Height = Rng.Height
End With
ActiveSheet.Name = "velH-velD-Glide"
'Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Open()
Set App = Application
End Sub
Attaching the xlam-file and a example CSV-file.
Installation Excel 2007
1. Download file.
2. Place xlam-file in your AddIns folder. C:\Users\ <USERNAME> \AppData\Roaming\Microsoft\AddIns
3. Open Excel
4. Click on the Excel "main button" in top left corner and choose |Excel options|.
5. Click on Add-Ins on the left.
6. At the bottom of the window it says Manage <Scrollbar> |Go|, choose Excel Add-Ins and press Go.
7. A window with avalible Add-Ins should pop up, mark Flysight and press |Ok|.
8. Close Excel and open the CSV file.