Creating Excel Charts

[Guest]

Good morning,
I have an application that transfers out a spreadsheet (5 records x 3
fields). I'd like to use Access VBA to create an Excel Chart on a new sheet
in Excel. I'm not too far into it. Here's my code so far:

Public Sub opencharts()
Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim strfilename As Variant
strfilename = "c:\temp" & Pickbox & ".xls"

Set xlapp = CreateObject("excel.application")
xlapp.Visible = True
Set xlworkbook = xlapp.Workbooks.Open(strfilename)
Set xlsheet = xlworkbook.Sheets(1)
?
?
?

End Sub

Can someone point me to the correct statements/code to make it happen?
Ideally, I could just run an Excel macro using Access VBA. However, since
each transferspreadsheet is run separately, and could be on someone else's
PC, I don't think I can use a macro.

Thanks in advance for the help!
Derek

 

[Alex Dybenko]

Hi Derek,
the best solution to find this - open excel, strart recording macro, add a
chart like you want, stop recording and see what code excel generate. now
you need to adjust this code to work in access

 

[Guest]

Thanks, Alex. I'll have to give it a shot!

Derek

 

[Guest]

Alex,
Thank you for the suggestion on the Excel Macro. This worked like a charm.
For some reason, some of my code (during subsequent iterations) results in
Run-Time errors... can you please check it out?

Public Sub opencharts(Pickbox As String)
Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim strfilename As Variant
pick2 = DMax("[Picker]", "tblPick", "")

strfilename = "c:\temp" & pick2 & ".xls"

Set xlapp = CreateObject("excel.application")
xlapp.Visible = True
Set xlworkbook = xlapp.Workbooks.Open(strfilename)
Set xlsheet = xlworkbook.Sheets(1)
xlapp.Columns("B:E").Select
xlapp.Charts.Add
xlapp.ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="100ths CF"
xlapp.ActiveChart.SetSourceData Source:=Sheets("tbl" & pick2 &
"totals").Range("B1:E6"), PlotBy:=xlColumns
xlapp.ActiveChart.Location Where:=xlLocationAsNewSheet
With xlapp.ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Slotting Analysis Based on
Recommendations"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Pick Level"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Pieces/Cubic Feet (100ths) Moved Weekly"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Number of
Skus"
End With
Set xlsheet = Nothing
Set xlworkbook = Nothing
Set xlapp = Nothing
DoCmd.DeleteObject acTable, "tbl" & pick2 & "totals"
End Sub

Thanks in advance!
Derek

 

[Alex Dybenko]

Hi Derek ,
at what line you get error? and what it says?

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Alex,
Thank you for the suggestion on the Excel Macro. This worked like a
charm.
For some reason, some of my code (during subsequent iterations) results in
Run-Time errors... can you please check it out?

Public Sub opencharts(Pickbox As String)
Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim strfilename As Variant
pick2 = DMax("[Picker]", "tblPick", "")

strfilename = "c:\temp" & pick2 & ".xls"

Set xlapp = CreateObject("excel.application")
xlapp.Visible = True
Set xlworkbook = xlapp.Workbooks.Open(strfilename)
Set xlsheet = xlworkbook.Sheets(1)
xlapp.Columns("B:E").Select
xlapp.Charts.Add
xlapp.ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="100ths CF"
xlapp.ActiveChart.SetSourceData Source:=Sheets("tbl" & pick2 &
"totals").Range("B1:E6"), PlotBy:=xlColumns
xlapp.ActiveChart.Location Where:=xlLocationAsNewSheet
With xlapp.ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Slotting Analysis Based on
Recommendations"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Pick
Level"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Pieces/Cubic Feet (100ths) Moved Weekly"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Number of
Skus"
End With
Set xlsheet = Nothing
Set xlworkbook = Nothing
Set xlapp = Nothing
DoCmd.DeleteObject acTable, "tbl" & pick2 & "totals"
End Sub

Thanks in advance!
Derek

Hi Derek,
the best solution to find this - open excel, strart recording macro, add
a
chart like you want, stop recording and see what code excel generate. now
you need to adjust this code to work in access