Problem Creating Excel Chart TextBoxes in Access 2010

[JonMulder]

Greetings,

I made up an Excel VBA module to automatically create fairly complex charts with several textboxes. I've since learned that I can invoke Excel inside Access and create the charts. This is going VERY well, and the charts look great. However, when I want to start adding some textboxes, I get an error message "Variable not defined". For some reason, it doesn't like "msoTextOrientationHorizontal". I have the Microsoft Excel 14.0 Object Library referenced.

Below is a distillation of my code:

 
Dim x1 As Excel.Application
Set x1 = New Excel.Application
'Lots of code here creating the chart object.
 x1.ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).Select
x1.Selection.Characters.Text = "State Well" & vbNewLine & "Number"

Any ideas? Thanks in advance for what light folks can shed on this!

Jonathan Mulder
Engineering Geologist


California Department of Water Resources

[June7]

Do you have reference set to the Microsoft Office 14.0 Object Library as well?

[JonMulder]

Yep, I think that was my problem. I added the Microsoft Office 14.0 Object Library and it works now! Now I can print some pretty complex water well hydrographs directly from my Access application.

Thanks for the help!

Jon Mulder
Engineering Geologist
California Department of Water Resources

[June7]

I assume you are aware Access 2007 has graphing capability, although not as versatile as what Excel can accomplish and perhaps your graphing requirements are too complicated for Access.

[JonMulder]

Yes, doing the graphs directly in Access was my first thought. But the hydrographs are fairly complex. The vertical axis varies depending upon the depth to groundwater (can vary from several feet to several hundred feet). The horizontal axis varies depending upon the "period of record" of groundwater measurements (several years to almost a century). Also, I need to plot several wells together on one chart if it's a multiple completion well. Actually I put most of the coding together in Excel over a year ago for a project to chart our water well dataloggers (which records water level every hour and some wells have a history going back 25 years -- lots of data!). So, getting it to work in my Access application took half a weekend, which is good. OTOH, I need to get a life and get out in the garden on weekends! <g>

[June7]

I also have graphs with variable ranges for the axes. I use VBA to set the min a max range limits and major and minor increments. If you already have Excel interface working satisfactorily then I can understand reluctance to go through development exercise again.