Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

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?
0
DCRAPACCESS
Asked:
DCRAPACCESS
  • 3
  • 3
1 Solution
 
DCRAPACCESSAuthor 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now