VBA read min & max values per sheet range to set primary and secondary axis chart

[bastian18]

Hello
I have an Access Db to export a query and create a chart based in worksheet ranges (B2 to last value down) and (C2 to last value down).
My command button my code creates a chart with primary and secondary axes values.
This is my code working well:

Sub cmdTransfer_Click()
Dim sExcelWB As String
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object


Set xl = CreateObject("excel.application")
'sExcelWB = "D:\testing2\"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")
'Set ch = xl.Charts.Add
Set ch = ws.Shapes.AddChart.Chart


With ch
    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLineMarkers
    .ChartGroups(1).GapWidth = 69
    'Chart Title
    .HasTitle = True
    .ChartTitle.Text = "Plot" 
    .Axes(xlValue).MajorGridlines.Delete
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
    .SetElement (msoElementLegendBottom)
End with


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -207
ActiveSheet.Shapes("Chart 1").IncrementTop -237.75
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.4708333333, msoFalse, _
        msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.48090296, msoFalse, _
        msoScaleFromTopLeft


xl.Visible = True
xl.UserControl = True


End Sub

My problem here is, I do not know how to tell VBA to read min & max values per range to set each axis values.
I have been googling for hours and hours with no success.


Please. Does anyone know of a workaround to this?


Thanks in advance

 

[xenou]

One simple solution would be to use a DMin() and DMax() function in MSAccess. The data is there too, so you can do that right in Access, not in Excel.

 

[bastian18]

xenou. I really appreciate your quick reply.

This is my first time where I have to deal with functions in MS Access VBA code.

I let you know if I can do it.
Cheers

 

[xenou]

Okay,
should be something like:

...
...
dim myMax as Long
dim myMin as Long
myMax = DMax("MyField","qry_task")
myMin = DMin("MyField","qry_task")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
...
...

Then you have those values in the variables myMax and myMin...

you could do vba on the excel sheet too, of course. But you'd need to get the column number of the data in Excel and then use a worksheet function on that column (it's been a while and Excel columns are very large now but I think Excel is smart enough to only search in the part of the column that has data).

 

[bastian18]

xenou. Many thanks for your reply

I tested your code with the query field names:

I tested your code with the query field names
myMax = DMax("Total_Sal", "qry_task")
myMin = DMin("Total_Sal", "qry_task")
myMax = DMax("Task_Val", "qry_task")
myMin = DMin("Task_Val", "qry_task")


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True

However, primary and secondary chart axes start at 0.
Cero is not in Total_Sal field either in Task_Val field in my query


I am tired of staring at the code and not knowing what to do.
I have been working on this for a week and this is driving me bonkers.

I really appreciate your help.

 

[Micron]

Not sure if this is why you get the values you do, but you only have 2 variables for which you seem to be trying to assign 4 values. As soon as your code reaches myMax for the 2nd time, whatever WAS there is gone and becomes the 2nd value. Thus you only end up with values for Task_Val. You need 4 variables? minX, maxX, minY, maxY.
Or you would have to process 2 variables for the X axis, then redo that code portion for the Y axis.

 

[xenou]

micron's comments are certainly relevant and you probably need to give the whole code to show how you are using the variables.

Also, if you want the min to be zero then simply make it zero.

Personally I normally allow Excel to auto-assign x and y min and max values as that is much the easiest. Setting your own values is somewhat subjective and in any case is tricky especially with data that is highly variable.

 

[bastian18]

Micron and xenou. Many thanks for your reply
This is my code.

Sub cmdTransfer_Click()
Dim sExcelWB As String
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myMax As Double
Dim myMin As Double


Set xl = CreateObject("excel.application")
sExcelWB = "D:\testing2\" & "qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")


Set ch = ws.Shapes.AddChart.Chart


With ch
    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLineMarkers
    .ChartGroups(1).GapWidth = 69
    
End With


xl.Visible = True
xl.UserControl = True


End Sub

Every time I run a query, MS Access VBA export the query and creates the Chart in the "qry_task" Worksheet. This code is fine


I'm looking for MS Access VBA to do the following:
For primary axis set minimum value and maximum value from query values.
For secondary axis set minimum value and maximum value from the query values.


Moreover, I have been studying from MS Access to Excel "early binding" and "late binding" tools but, to date I can not understand how to use such tools to assign axis minimum and axis maximum values.

Many thanks in advanced

 

[Micron]

Excel vba isn't my strong point anymore. Most anything I need to do I have to lookup. However, the code you posted doesn't look like it could be what you tried that didn't work. I think what you reported that didn't work is the code xenou was suggesting you post. I'm rusty on Excel charts, so I don't know if you're trying to affect the X and Y axes or you have two Y axes when you refer to a secondary axis. To me, you're talking about 2 Y axes, which I think requires you to work with the xlAxisGroup property. However, my first post presumed you were trying to affect X and Y axes separately. While I'm fairly confident you would need 4 variables regardless, it's not clear to me what it is you want to affect, and you didn't address my observation that your code assigns values to 2 different variables, only to over-write them immediately after.

I would describe late or early binding as a way of using the correct dll libraries to access their objects, methods or whatever. An example would be that if different users have different versions of Access, then late binding allows Access to figure out the name and/or version of the library needed at run time. Whichever version was installed with Access will be used. If you do early binding, your code attempts to use the method or property pertaining to the references that have been set. So if you code to use a certain version of Excel and a user has an earlier version, the procedure may fail when using early binding.

 

[bastian18]

Micron.
I apologize if my messages are confusing.
Yes, I have two Y axes with different values. One axis has number format with thousands and the other axis has format number in ones.
I am Lookin for how to affect those axes setting with minimum and maximum values.

Many thanks for your explanation about early and late binding.

Cheers