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" wrote:
> 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 Dybenko (MVP)
> http://Alex.Dybenko.com
> http://www.PointLtd.com
>
>
> "Derek Wittman" <(E-Mail Removed)> wrote in message
> news:9DFFF798-7394-46A4-8944-(E-Mail Removed)...
> > 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
> >
>
>
>
Similar Threads
