fradaems
New Member
- Joined
- Oct 17, 2015
- Messages
- 4
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>
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>