Access 2007: error setting axes minimum value in Excel file

[fradaems]

I've been searching for the cause of an error all day, but can't see it.

Summary: an Access form creates an Excel file with 2 worksheets: one for data and another one for a graph. Once the graph is completed it is saved as a .png file. All working fine, except one thing. I want all graphs to have the Y axe start at zero. Most do, some don't (because of minimal difference between min. and max. value). Hence, I wanted to set the minimal value to always be zero.

Coding (parts of the function):

a) related definitions
<code>
Dim oXL As Object ' Excel application
Dim oBook As Object ' Excel workbook
Dim oSheet1 As Object ' Excel Worksheet
Dim oSheet2 As Object ' Excel Worksheet
Dim oChart As Object ' Excel Chart
Dim cNumRows As Integer ' Number of Series
</code>
b) creation of graph (note oSheet1 is the worksheet containing the 2 columns of information (column A a date, column B a percentage) - this coding works
<code>
oBook.Sheets.Add
Set oSheet2 = oBook.Worksheets.Item(1)
Set oChart = oSheet2.ChartObjects.Add(1, 1, 900, 400).Chart
oChart.SetSourceData Source:=oSheet1.Range("A2").Resize(cNumRows - 1, 2)
With oSheet2.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = recdef![Categorie] & " - ( " & Format(counter, "0") & " fondsen )"
.SeriesCollection(1).Name = "Percentage"
End With
</code>
c) various attempts to set the Y axe value to zero (changing some lines from comment to code or reversed). All fail on the 'With .Axes' coding line (method failed - sorry my software is in Dutch language so I don't know the exact message in English). The 3 first commented lines contain what the Excel macro gave when recording the manual change, which doesn't work either).
<code>
' ActiveSheet.ChartObjects("Grafiek 1").Activate
' ActiveChart.Axes(xlValue).select
' ActiveChart.Axes(xlValue).MinimumScale = 0

' With oChart
With oSheet2.ChartObjects(1).Chart
With .Axes(xlValue)
' With .Axes(xlValue, xlPrimary)
.MinimumScale = 0
End With
End With

TIA for your help!

</code>

 

[Micron]

Just taking a stab here. oChart As Object might have to be oChart as ChartObject. Also, I think ChartObjects is a collection, not a specific chart, so I don't think you can refer to it with an index the way you are. The index would be for the chart object. I think that's why you're getting a message about a failed method. Maybe after activating the correct sheet:

With
  ActiveSheet.ChartObjects.oChart.Chart(1).Axes
  .MinimumScale =
  .MaximumScale = 
  .MajorUnit = 
End With

I would not bet money on my air code, though.

 

[fradaems]

Thanks for your help. I meanwhile had given up and applied a simple workaround instead. I've added an additional group of data (i.e. last date plus one with a zero percentage) and the graph now shows what I wanted it to look like, starting with zero minimum value on the Y axe.

 

[Norie]

Where are you declaring the Excel VBA constants xlValue and xlPrimary?

 

[fradaems]

Thanks for your help, which gives me an idea. It's not declared anywhere. I should maybe add a reference to Excel? I see a 'Microsoft Excel 12.0 Object Library' which is not ticked for the moment. I'll give it a try tomorrow (where I live it's high time to go to bed now - working day tomorrow again).

 

[Norie]

You don't really need to add a reference to Excel.

Just goto the Object Browser in Excel VBA, search for xlValue and xlPrimary and copy their declarations to your Access code.

 

[fradaems]

Adding the reference mentioned in my previous message listed above did solve the problem!

 

[Norie]

Glad to hear that.

I've had similar problems in the past when using late-binding to automate other applications.

One thing I found invaluable was to add Option Explicit at the top of every module using the Require variable declaration setting under Tools>Options.

With that set up any undeclared (or misspelt) variables were soon flagged up.