Solved

Creating Excel using vba in Access

Posted on 2011-03-14
6
569 Views
Last Modified: 2012-05-11
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
Comment
Question by:DCRAPACCESS
  • 3
  • 3
6 Comments
 

Accepted Solution

by:
DCRAPACCESS earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:DCRAPACCESS
Comment Utility
>Found the solution my self
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>> 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
This will teach me to type faster . LOL

Sid
0
 

Author Comment

by:DCRAPACCESS
Comment Utility
What about formatting the color of the LegendKeys?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now