PDA

View Full Version : Solved: Activate workbook with excel add-in



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.

mdmackillop
10-21-2010, 10:58 AM
Not tried your code yet, but you can't save more than one sheet in a CSV file, which is basically a text document.

Ago
10-21-2010, 11:02 AM
I know, the idea is that once all the stuff is done it should either save itself as a xls file or "force" the user to do so.

The CSV file is the raw data that the GPS gives me, and not very eyefriendly so you have to resave it as xls anyways.

mdmackillop
10-21-2010, 12:10 PM
Code to date. Some issues though with setting up the chart after the Stop line.
As you can see, I'm having issues with the formatting!



Private Sub App_SheetActivate(ByVal Sh As Object)


Dim wkbName As String
Dim LastRow As
Double
Dim wb As Workbook
Dim ws As
Worksheet



Application.EnableEvents = False
On
Error GoTo exits

Set wb =
ActiveWorkbook
wkbName = wb.Name
extension = Mid(wkbName, InStr(wkbName, "."))
shtName =
Left(wkbName, InStr(wkbName, ".") - 1)


If UCase(extension) = ".CSV" And Range("A1").Value =
"time" Then ',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


Range("I:I, G:G, D:D, J:J").Font.Bold = True
Range("D:D,
J:J").NumberFormat = "0"


Range("A1").Select



Set ws =
wb.Sheets.Add(After:=wb.Sheets(1))

ws.Shapes.AddChart.Select
Stop

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"
exits:
Application.EnableEvents =
True

'Application.ScreenUpdating =
True


End Sub


Private Sub App_SheetActivate(ByVal Sh As Object)
Dim wkbName As String
Dim LastRow As Double
Dim wb As Workbook
Dim ws As Worksheet

Application.EnableEvents = False
On Error GoTo exits

Set wb = ActiveWorkbook
wkbName = wb.Name
extension = Mid(wkbName, InStr(wkbName, "."))
shtName = Left(wkbName, InStr(wkbName, ".") - 1)

If UCase(extension) = ".CSV" And Range("A1").Value = "time" Then ',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

Range("I:I, G:G, D:D, J:J").Font.Bold = True
Range("D:D, J:J").NumberFormat = "0"

Range("A1").Select

Set ws = wb.Sheets.Add(After:=wb.Sheets(1))
ws.Shapes.AddChart.Select
Stop
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"
exits:
Application.EnableEvents = True

'Application.ScreenUpdating = True
End Sub

Ago
10-21-2010, 07:37 PM
Thank you very much!

I managed to get passed that line but now im stuck at another place.

Set ws = wb.Sheets.Add(After:=wb.Sheets(1))
ws.Shapes.AddChart.Select

With ws.ChartObjects("Chart 1")
.Chart.ChartType = xlLine
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(1).Name = "=""velH"""
.Chart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(2).Name = "=""velD"""
.Chart.SeriesCollection(2).Values = "='" & shtName & "'!$I$3:$I$" & LastRow
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(3).Name = "=""hMSL"""
.Chart.SeriesCollection(3).Values = "='" & shtName & "'!$D$3:$D$" & LastRow
' ActiveSheet.ChartObjects("Chart 1").Activate
.Chart.SeriesCollection(3).AxisGroup = 2
End With
Set Rng = ws.Range("A1:R27")
_______________________________________________________________
With ws.ChartObjects("Chart1").Parent
.Left = Rng.Left
.Width = Rng.Width
.Top = Rng.Top
.Height = Rng.Height
End With
ActiveSheet.Name = "velH-velD-hMSL"


Its where i set the size of the chart.
I dont understand why it wont work.
It used to be activechart.parent, but that wont work, so using the same kind of code as used above it should be.
With ws.ChartObjects("Chart1").Parent
or
With ws.ChartObjects("Chart1").chart.Parent

But none of them work.
Have to get to work now.
Thank you very much for your help so far

frank_m
10-22-2010, 02:03 AM
Looks like you may have the Chart name mispelled. "Chart1" probably is "Chart 1"

The Macro below works for me.
Sub SizeChartToRange()
Dim ChartObj As ChartObject
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1:R27")

Set ChartObj = ActiveSheet.ChartObjects(1)'<- 1st Chart on Sheet
'Set ChartObj = ActiveSheet.ChartObjects("Chart 1")'<-ok if sure of Chart name
With ChartObj
.Height = Rng.Height
.Width = Rng.Width
.Top = Rng.Top
.Left = Rng.Left
End With
End Sub

Ago
10-22-2010, 07:00 AM
Yes Frank you are right i misspelled the name and forgot chart in the command.
The correct command should be

With ws.ChartObjects("Chart 1").Chart.Parent


Thank you both for your help!