locked
GRAPH.EXE thowing an exception when setting range values beyond row 28 after Upgrading to MS Office 2013 RRS feed

  • Question

  • I have a MS Access database that was written in MS Access 2007.

    It has a report with a chart created with MSGraph.Chart.8 class.

    The chart is populated by writing values to cells in the Graph.Datasheet.

    It has been working fine until upgrading to MS Office 2013.

    It now reports the following error:

    Microsoft Visual Basic
    Run-time error '-2147417851 (80010105)':
    Method '_Default' of object 'Range' failed

    The Err object descrition is "Automation error The server threw an exception. '

    When I step into the subroutine, it errors after the 28th row. If there are 28 or less rows, it works fine. It seems to do this regardless of the values being written to the cells.

    I have tried running a repair on the installation. This had no effect.

    I have checked the references in the other objects. Everything seems to be pointing to the correct location.

    Is there something new limiting the Graph object?

    Monday, July 14, 2014 7:17 PM

Answers

  • After consulting with Microsoft Support, this has been resolved.

    The error occurs within MSGraph when it attempts to convert from a variant data type.

    To prevent this use Dim statements to declare a more specific datatype like Date, String, or Double.

    You can also use any of the conversion functions like CDate, CStr, or CDbl.

    So by specifically converting the fields/variables to a specific type before passing them to MSGraph the issue will be resolved.

    Posted with thanks to the support team at Microsoft.

    Tuesday, August 12, 2014 5:26 PM

All replies

  • Hi William,

    Could you please post the VBA code snippet which gives this error?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 15, 2014 7:54 AM
  • The following is a section of the code in the report.

    Option Compare Database
    
    Public Sub FillChart(Cluster As String, dtmDate As Variant)
    '******************************************************************************
    'Purpose:    Fills and renders the percentage occupancy chart for a given date
    'Inputs:     Cluster        Primary Key for Lease
    '            dtmDate        Date used to select begining time frame
    'Outputs:    None
    'Returns:    None
    'Assumes:    [list tables, queries, and fields required]
    'Effects:    prepares the chart
    'Notes:      See Assumes
    '            Configuration Variables
    'History:    written 12/03/2012 - wcs
    '******************************************************************************
    
        Dim chrtOcc As Graph.Chart
        Dim dsOcc As Graph.DataSheet
    
        Dim rstOcc As Recordset
        Dim rstEvents As Recordset
    
        'temporary objects
        Dim qdef As QueryDef
        
    'Begin Get the data set required
        
        'OccupancyOverTime Query
        Set qdef = CurrentDb.QueryDefs("Query3B_Cluster")
        qdef.Parameters(0) = Cluster
        Set rstOcc = qdef.OpenRecordset
        
    'End   Get the data set required
    
        'set objects
        Me.graphPercentOccupancy.Activate
        Set chrtOcc = Me.graphPercentOccupancy.Object
        Set dsOcc = chrtOcc.Parent.DataSheet
        
        'clear existing data from datasheet
        dsOcc.Cells.Clear
    
    'Begin Transfer of OccupancyOverTime Recordset
    
        dsOcc.Cells(1, 2) = "AUSF"
        dsOcc.Cells(1, 3) = "PUSF"
        
        I = 1
        PreviousAUSF = 0
        PreviousPUSF = 0
        
        FinalAUSF = 0
        FinalPUSF = 0
    
        If Not (rstOcc.BOF And rstOcc.EOF) Then
            rstOcc.MoveFirst
            Do While Not rstOcc.EOF
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate")
                dsOcc.Cells(I, 2) = PreviousAUSF
                dsOcc.Cells(I, 3) = PreviousPUSF
                
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate")
                FinalTDate = rstOcc("TDate")
                dsOcc.Cells(I, 2) = rstOcc("AUSF")
                PreviousAUSF = rstOcc("AUSF")
                dsOcc.Cells(I, 3) = rstOcc("PUSF")
                PreviousPUSF = rstOcc("PUSF")
                
                rstOcc.MoveNext
            Loop
            'write final row to datasheet
            I = I + 1
            dsOcc.Cells(I, 1) = FinalTDate
            dsOcc.Cells(I, 2) = FinalAUSF
            dsOcc.Cells(I, 3) = FinalPUSF
            
        Else
            'No data to chart
            GoTo ExitNoData
        End If
        
    'End   Transfer of OccupancyOverTime Recordset
    '*****
    ' other commands for formatting have been omitted
    '*****
    
    ExitNoData:
    
    End Sub
    

    Note:

    The code above is part of a Report that is used as a subReport. It is called by the parent report in the On Format event.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        
        Dim OC As Report_Report3B_Cluster
        Set OC = Me.Report3B_Cluster.Report
        OC.FillChart Me.Cluster, Forms("frmReportSettings").txtReportingDate
    
    End Sub

    A sample of the data being passed to the chart is as follows:

      

    Query3B_Cluster

      
      

    TDate

      
      

    AUSF

      
      

    PUSF

      

    10/1/2002

    39011

    0

    6/14/2003

    49071

    0

    9/1/2008

    55310

    0

    12/1/2008

    70998

    0

    9/1/2010

    75455

    0

    2/9/2012

    82760

    0

    10/1/2012

    82760

    0

    11/2/2012

    72700

    0

    9/1/2013

    72700

    0

    3/17/2014

    57012

    0

    9/1/2015

    52555

    4457

    10/1/2017

    13544

    4457

    9/1/2018

    7305

    4457

    9/1/2020

    7305

    0

    1/8/2022

    0

    0

    Tuesday, July 15, 2014 12:00 PM
  • So which line gives you the error message? what is the record(28th row) that cause the error?

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 16, 2014 6:03 AM
  • 1. >>
    It has a report with a chart created with MSGraph.Chart.8 class.
    <<

    Chart.8 belongs to MSGraph 97, AFAIK.  Shouldn't your database use a later version?  I would have expected at least version 12 which is the version number for Office 2007 which was used to develop the database.

     

    2. >>
    Microsoft Visual Basic
    Run-time error '-2147417851 (80010105)':
    Method '_Default' of object 'Range' failed

    The Err object descrition is "Automation error The server threw an exception. '

    When I step into the subroutine, it errors after the 28th row. If there are 28 or less rows, it works fine. It seems to do this regardless of the values being written to the cells.
    <<

    Personally, I have not used code to pupulate an existing Datasheet associated with a Chart Object but AFAICS, there is nothing to add additional rows in your code. For example, if the Chart Object and its associated Datasheet were designed originally with only 28 rows of data but now you have more than 28 rows of data to be written into the Datasheet but there is nothing to add additional rows of cells in the code.  As soon as row 29 of the Datasheet is referenced in your code, your code will fail because the specified row does not exist.

    If you check the ObjectBrowser, the syntax for the (hidden) Property of the Graph.Range Object is

    _Default([RowIndex], [ColumnIndex])

    so your error message seems to be consistent with the scenario I described above. 

     


    Van Dinh

    Wednesday, July 16, 2014 9:50 AM
  • Cailen,

    The error occurs on the line indicated:

            Do While Not rstOcc.EOF
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate")
                dsOcc.Cells(I, 2) = PreviousAUSF
                dsOcc.Cells(I, 3) = PreviousPUSF
                
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate") 'This line when I is 29
                FinalTDate = rstOcc("TDate")
                dsOcc.Cells(I, 2) = rstOcc("AUSF")
                PreviousAUSF = rstOcc("AUSF")
                dsOcc.Cells(I, 3) = rstOcc("PUSF")
                PreviousPUSF = rstOcc("PUSF")
                
                rstOcc.MoveNext
            Loop
    

    The chart is a stepped and stacked area chart. To achieve this twice the number of records are written to the datasheet. Note the the loop writes two rows for each record.

    I have changed the dataset and it still errors on the same row.

    Wednesday, July 16, 2014 11:57 AM
  • Van,

    You are thinking the same things I am.

    1> The GRAPH.EXE is version on 2007 is 12.0.6606.1000. When viewed in the Object Browser, it is Microsoft Graph 12.0 Object Library. In the properties window it is shown as MSGraph.Chart.8.

    In 2013 the GRAPH.EXE file is version 15.0.4420.1017. when viewed in the Object Browser it is Microsoft Graph 15.0 Object Library. In the properties window it is still shown as MSGraph.Chart.8.

    I don't know why it places it as MSGraph.Chart.8 by default when you insert one. even in 2013.

    As near as I can tell, the object has not changed much in years.

    2> I thought it might be a limitation of the number of rows in the datasheet at design time. (Something that has not been a problem in the past.) I opened the object and populated the datasheet with more than 50 rows and saved the changes. It still errored out.

    I take the syntax of the _Default to be the value at the row and column indexes specified. It works for both get and set.

    Am I still not reading that right?

    Update:

    I set the error handling to On Error Resume Next inside the loop and turned it back off with On Error GoTo 0 once outside.

    The report runs and displays all the data in the chart.

    So, While it was reporting some error, it does not seem to be having an effect on it being able to generate the result.

    This is less than optimal solution. I am not a fan of turning off error handling.



    • Edited by William Sessums Wednesday, July 16, 2014 2:34 PM Addtional Information
    Wednesday, July 16, 2014 12:37 PM
  • >>
    I opened the object and populated the datasheet with more than 50 rows and saved the changes. It still errored out.
    <<

    and

    >>
    dsOcc.Cells(I, 1) = rstOcc("TDate") 'This line when I is 29
    <<

    The above "I is 29" correspond to the row 58 in your Datasheet if I read your code correctly.  Thus, your code will error out if you only have 57 existing rows in the datasheet???

     


    Van Dinh

    Thursday, July 17, 2014 4:04 AM
  • It's the other way around. Each record writes two rows in the datasheet.

    'Begin Transfer of OccupancyOverTime Recordset
    
        dsOcc.Cells(1, 2) = "AUSF" ' write headers to row 1
        dsOcc.Cells(1, 3) = "PUSF"
        
        I = 1
        PreviousAUSF = 0
        PreviousPUSF = 0
        
        FinalAUSF = 0
        FinalPUSF = 0
    
        If Not (rstOcc.BOF And rstOcc.EOF) Then
            rstOcc.MoveFirst
            Do While Not rstOcc.EOF
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate") ' write one row with current date
                dsOcc.Cells(I, 2) = PreviousAUSF
                dsOcc.Cells(I, 3) = PreviousPUSF
                
                I = I + 1
                dsOcc.Cells(I, 1) = rstOcc("TDate") ' write second row with current date
                FinalTDate = rstOcc("TDate")
                dsOcc.Cells(I, 2) = rstOcc("AUSF")
                PreviousAUSF = rstOcc("AUSF")
                dsOcc.Cells(I, 3) = rstOcc("PUSF")
                PreviousPUSF = rstOcc("PUSF")
                
                rstOcc.MoveNext
            Loop
            'write final row to datasheet
            I = I + 1
            dsOcc.Cells(I, 1) = FinalTDate
            dsOcc.Cells(I, 2) = FinalAUSF
            dsOcc.Cells(I, 3) = FinalPUSF
    

    so with the sample data the second time it trys to write the TDate in record 14 it is writing to row 29 in the datasheet.
    Thursday, July 17, 2014 5:06 AM
  • Sorry. Got my logic wrong way around.

     


    Van Dinh

    Thursday, July 17, 2014 6:12 AM
  • It's hard to identify the root cause of the error, could you please make a sample Access database and upload in OneDrive, so that I can test the VBA code and reproduce this issue? Thanks!


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Caillen Friday, July 18, 2014 8:03 AM
    Friday, July 18, 2014 8:02 AM
  • I have stripped down a copy of the database with a token bit of data that demonstrates the problem.

    It is posted on SkyDrive at this location:

    MinumumTest.accdb

    After opening the database, open the form called frmReportSettings

    then run print preview of Report5

    you can then run debug pressing F8 to step into the subroutine.

    If you watch the value for "I" you see what I mean.

    Please let me know if you have any problems downloading the file.

    NOTE:

    I have tried editing the references to add the .Value property rather than relying on the _Default.

    This seems to work better but it still intermittantly errors out.

    Is there perhaps some timing issue with OLE?


    Friday, July 18, 2014 3:35 PM
  • Hi William,

    I've reproduced your problem, and if I remove some of the references like "Microsoft Excel 15.0 Object Library" from the VBA editor, then the error message will occur in other VBA code lines.

    I'll involve some other engineers in your case, if there're any updates, we'll come back.

    Thanks for your patience.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 22, 2014 6:25 AM
  • Hello William,

    I am able to reproduce the issue with the sample that you shared. What I noticed is that the issue can  occur at any place and not specific to 29th row.

    This question falls into paid support category which requires more in-depth level of support. Please visit the below link to see the various paid support options that are available to better meet your needs.

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged.

    Thanks,

    Sreerenj G Nair

    Wednesday, July 23, 2014 10:00 PM
  • After consulting with Microsoft Support, this has been resolved.

    The error occurs within MSGraph when it attempts to convert from a variant data type.

    To prevent this use Dim statements to declare a more specific datatype like Date, String, or Double.

    You can also use any of the conversion functions like CDate, CStr, or CDbl.

    So by specifically converting the fields/variables to a specific type before passing them to MSGraph the issue will be resolved.

    Posted with thanks to the support team at Microsoft.

    Tuesday, August 12, 2014 5:26 PM