Formatting Excel Chart from Access VBA (1 Viewer)

[Packy427]

Hi all,

I'm trying to format an Excel chart from an Access module. My goal is to have a temporary Excel workbook that appears to show data, and I don't want to have another Excel file as a template if at all possible.

As of now I can successfully add all of the required data to the excel workbook and create the chart as the default bar graph. The only problem is that I can't format the chart to change any of the properties. I recorded a macro from Excel with the changes I wanted to make to the format, but Access VBA doesn't recognize the properties. For example it does not recognize xlXYScatterLines for chart type or xlBottom for legend position even when it's taken directly from the recorded Excel macro. Is there a difference between Access and Excel VBA? Is there different syntax/statements I should be using to manipulate the Excel graph from Access?

I have these references selected: Excel 15.0 Object Library, Microsoft Graph 15.0 Object Library, Microsoft Office 15.0 Object Library, as well as the defaults. Is there any other references that should be added?

The frustrating part is it was working fine on another computer with the same versions of Office (2013), but after switching now it won't work :banghead:

I have it set up as late binding, also tried early binding but to no avail. I'm hoping I'm just missing something minor that I haven't flushed out yet.

Option Explicit
Public Function ExportRecordsetToExcel(rsLeft As DAO.Recordset, rsRight As DAO.Recordset, numRecords As Integer) 

On Error GoTo handler:
    'CREATE VARIABLES
    Const xlUP              As Long = -4162 'excel constants if used need to be referenced manually!
    Dim oExcel              As Object   'Excel application
    Dim oBook               As Object   'Excel workbook
    Dim oSheet              As Object   'Excel worksheet
    Dim oChart              As Object   'Excel Chart

    'ERROR CHECK
    If rsLeft.BOF And rsLeft.EOF Then
        Exit Function 'no data to write
    Else
        rsLeft.MoveFirst
        rsRight.MoveFirst
    End If

    'Define Variables
    Set oExcel = New Excel.Application
    oExcel.Visible = True 
    Set oBook = oExcel.Workbooks.Add 

    'Add data to cells of the first worksheet in the new workbook.
    Set oSheet = oBook.worksheets(1)

    'Add headers
    oSheet.range("A1").Value = "Date Recorded"
    oSheet.range("B1").Value = "LH Measure"
    oSheet.range("C1").Value = "RH Measure"
    
    'Copy data from recordset
    oSheet.range("A2").copyfromrecordset rsLeft
    oSheet.range("C2").copyfromrecordset rsRight
    
    'Add Chart Object
    Set oChart = oSheet.ChartObjects.Add(50, 40, 600, 400).Chart
    oChart.SetSourceData Source:=oSheet.range("A2").Resize(numRecords, 3)

    'Format Chart
    oChart.ChartType = xlXYScatterLines
    oChart.Legend.Position = xlBottom
    
    
    ' Make Excel Visible:
    oExcel.Visible = True
    oExcel.UserControl = True

'tidy up
    Set oSheet = Nothing
    Set oBook = Nothing
    Set oExcel = Nothing

Exit Function
handler:

End Function

Any help would greatly appreciated
-Pat

 

[Ranman256]

It seems to me, you would export the data to excel sheet.
The chart updates itself based on the sheet.

 

[Packy427]

Hey all,

So I got it to work! I used the object browser in the Access VBE to find the property that I wanted and then located the corresponding constant.

xlXYScatterLines was 74
xlLegendPositionBottom was -4107

Plugged those in instead of the property name and it worked like a charm.

 

[Rx_]

Was the Reference in the Tools set to Excel 2015 and the error happened when running on on Excel 2013?
This kind of issue can be due to an Excel Type Mismatch based on version or even Service Pack.
For anyone using MS Access using Excel automation for reports (I resemble that remark) it is very important that the Development Workstation has exactly the same MS Office Version and SP as the Citrix Applicaiton Server.

In your case, try to select both Excel 2015 and Excel 2013 objects on the Tools Reference.
Of course, the integer (74) values as you used will work across versions until Microsoft changes them. We use to expect Microsoft to keep some kind of version compatibility. Many of us are going back to the numbers instead of the xlconstants.
This site has the Excel Chart type enumuration:
http://billietconsulting.com/2013/11/creating-charts-with-vba-in-excel/
Amazing, they reference wiki for this instead of Microsoft. Sign of the times?