ACCESS FEED: [MS_AccessPros] RE: question cause I"m in a hurry (Excel Range in VBA)

Kamis, 28 Mei 2015

[MS_AccessPros] RE: question cause I"m in a hurry (Excel Range in VBA)

It worked:

Public Sub SetStackedSeries(appexcel As Object, strDatasheet As String, strChartName As String, strChartTitle As String, intLastRow As Integer, intLastCol As Integer)

With appexcel

.sheets(strChartName).Select

.activechart.ChartArea.Select

.activechart.HasTitle = True

.activechart.ChartTitle.characters.Text = strChartTitle

' set the data to the range as calculated

.activechart.SetSourceData Source:=.sheets(strDatasheet).Range(.sheets(strDatasheet).cells(1, 1), .sheets(strDatasheet).cells(intLastRow, intLastCol))

.sheets(strChartName).Select

End With

End Sub

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, May 28, 2015 11:40 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] RE: question cause I"m in a hurry (Excel Range in VBA)

Now I have an application or object defined error:

But the code is changed to this:

Public Sub SetStackedSeries(appexcel As Object, strDatasheet As String, strChartName As String, strChartTitle As String, intLastRow As Integer, intLastCol As Integer)

With appexcel

.sheets(strChartName).Select

.activechart.ChartArea.Select

.activechart.HasTitle = True

.activechart.ChartTitle.characters.Text = strChartTitle

' set the data to the range as calculated

.activechart.SetSourceData Source:=.sheets(strDatasheet).Range(.cells(1, 1), .cells(intLastRow, intLastCol))

.sheets(strChartName).Select

End With

End Sub

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, May 28, 2015 11:36 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] RE: question cause I"m in a hurry (Excel Range in VBA)

Had to add to the subject. I like more clarity.

Here’s what I have so far but am getting an error of invalid property or method.

Public Sub SetStackedSeries(appexcel As Object, strDatasheet As String, strChartName As String, strChartTitle As String, intLastRow As Integer, intLastCol As Integer)

With appexcel

.sheets(strChartName).Select

.activechart.ChartArea.Select

.activechart.HasTitle = True

.activechart.ChartTitle.characters.Text = strChartTitle

' set the data to the range as calculated

With .sheets(strDatasheet)

.activechart.SetSourceData Source:=.Range(.cells(1, 1), .cells(intLastRow, intLastCol))

End With

.sheets("Chart").Select

End With

End Sub

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, May 28, 2015 11:12 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] question cause I"m in a hurry

Pros,

I’m writing data in to an excel spreadsheet and at the end need to set the chart (stacked bar) data to that of what I had just added to.

I add chronologically, so I’m keeping track of the last row and column – integer wise from activecell.row and .column.

So, now I need to set the series on the chart and the range business seems to think that I need (“A1:C12”) type of format and can’t even see where I can translate that.

I also can’t set each series individually because they are dynamic and based on a recordset from a query and I never know how many I’ll get. I have to use the range method.

Please help.

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.