Creating Excel using vba in Access

Hi Experts,

I have a Clustred bar chart in a Excel file that i create using vba code in Access. Every thing is working fine, but some settings is very hard to control.

I want to do this to the bar chart:
ActiveChart.SetElement (msoElementPrimaryCategoryAxisWithoutLabels)

AND

ActiveChart.SetElement (msoElementDataLabelOutSideEnd)

But as you all know, this will give a error in access :-(

So now to the big question, how do make these settings from Access using vba code?
DCRAPACCESSAsked:
Who is Participating?
 
DCRAPACCESSConnect With a Mentor Author Commented:
Found the solution by an accident :-)

This part is good enough:
ActiveChart.SetElement()

It is this part that Access cannot understand:
msoElementPrimaryCategoryAxisWithoutLabels

I have found this webpage, that translate "msoElementPrimaryCategoryAxisWithoutLabels" into a number:
https://msofficefornet.svn.codeplex.com/svn/01%20-%20Common/MsoChartElementType.vb

Writing "ActiveChart.SetElement(350)" works like a charm :-)
0
 
DCRAPACCESSAuthor Commented:
>Found the solution my self
0
 
SiddharthRoutCommented:
>>> Found the solution by an accident :-)

I had finished typing and pressed refreshed to see if someone had answered and then saw your post :)

Do this

Declare this at the beginning of the code as the msoElementPrimaryCategoryAxisWithoutLabels and msoElementDataLabelOutSideEnd  are Excel Constatnts :)

Const msoElementPrimaryCategoryAxisWithoutLabels As Long = 350. 2445
Const msoElementDataLabelOutSideEnd As Long = 205

Sid
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SiddharthRoutCommented:
This will teach me to type faster . LOL

Sid
0
 
DCRAPACCESSAuthor Commented:
What about formatting the color of the LegendKeys?
0
 
SiddharthRoutCommented:
Sorry. had stepped out for a moment.

Do you mean this? I am just typing from memory.

Dim objSeries As Object

With ActiveChart
    For Each objSeries In .SeriesCollection
        '~~> Set necessary formatting here.
        objSeries.Border.colorIndex= 3 '<~~ color Red
    Next
End With

Open in new window


Sid
0
All Courses

From novice to tech pro — start learning today.