none
Application-defined or object-defined error RRS feed

  • Question

  • Good morning all,

    I'm developing for a client a Project-based reporting solution that pushes task data into Excel and summarizes it. The code works fine when run only once, but if I try to run it a second time I receive the following error:

    Run-time error '1004':
    Application-defined or object-defined error

    My only solution is to exit out of both Project and Excel, re-open the two Project files I'm interesting in comparing, and run the code anew. I've not had any success with researching and resolving the issue, which seems to be related to the Excel workbook's line charts.

    The snippet which throws the error follows:

        xlworkbook.Worksheets(worksheetName).ChartObjects(chartName).Activate
            
        With ActiveChart
            For counter = 0 To (endRow - beginRow)
                targetRow = counter + beginRow
                Set rangeValues = xlWorksheet.Range("N" & targetRow & ":" & "S" & _
                    targetRow)
                .SeriesCollection.Add source:=rangeValues
                .SeriesCollection(counter + 1).Name = xlWorksheet.Range("A" & _
                    targetRow).Value
            Next counter
            
            .HasTitle = True
            .ChartTitle.Text = chartName & ": " & itemTotal
            .SeriesCollection(1).XValues = "='Historical Details'!$N$6:$S$6"
        End With
        
        Set targetChart = Nothing

    The With ActiveChart is the line that throws the error, but if I remove the With block the code still errors out.

    I've also tried setting all objects to Nothing on completion of processing, to no avail.

    As always, any suggestions are greatly appreciated.

    Thanks,

    John


    John C. Johnson





    • Edited by JayCJohnson Tuesday, September 18, 2012 3:37 PM
    Tuesday, September 18, 2012 3:32 PM

Answers

  • John,

    First of all, do you have the latest service packs installed for both Project and Office? If not, go to, http://technet.microsoft.com/en-us/office/ee748587, download and install them.

    Do you have a reference set for the Excel object library? If not, set it.

    It's a little hard to tell what might be wrong from the code snippet you show but one thing I know that can cause a macro to run fine one time but then fail on subsequent runs is creating a custom view, table, filter, etc. (Project) in the code. If that new item is not deleted when the macro is finished, or no provision to check if the item already exists, an attempt to create it a subsequent time generates an error. I don't know whether you are doing something like that in the Excel part of the code but you might check.

    Hope this helps.

    John

    • Marked as answer by JayCJohnson Tuesday, September 18, 2012 4:54 PM
    Tuesday, September 18, 2012 3:43 PM

All replies

  • John,

    First of all, do you have the latest service packs installed for both Project and Office? If not, go to, http://technet.microsoft.com/en-us/office/ee748587, download and install them.

    Do you have a reference set for the Excel object library? If not, set it.

    It's a little hard to tell what might be wrong from the code snippet you show but one thing I know that can cause a macro to run fine one time but then fail on subsequent runs is creating a custom view, table, filter, etc. (Project) in the code. If that new item is not deleted when the macro is finished, or no provision to check if the item already exists, an attempt to create it a subsequent time generates an error. I don't know whether you are doing something like that in the Excel part of the code but you might check.

    Hope this helps.

    John

    • Marked as answer by JayCJohnson Tuesday, September 18, 2012 4:54 PM
    Tuesday, September 18, 2012 3:43 PM
  • John,

    Thanks for the quick reply - I'm always amazed at how promptly help arrives on the forums.

    Your reply helped me to isolate the issue. I replaced:

        xlworkbook.Worksheets(worksheetName).ChartObjects(chartName).Activate
            
        With ActiveChart

    with

        With xlWorkbook.Worksheets(worksheetName).ChartObjects(chartName).Chart
    

    and everything's working like a champ.

    Thanks!


    John C. Johnson

    Tuesday, September 18, 2012 4:54 PM
  • John,

    You're welcome and thanks for the feedback.

    John

    Wednesday, September 19, 2012 12:22 AM