Creating Excel Charts

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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 ,
at what line you get error? and what it says?

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


Derek Wittman said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top