none
intermittent code problem RRS feed

  • Question

  • using 2007 - I have a user form that gathers data concerning the DCMA 14 Point Metric.  After the calculation, the user has the option to export the data to Excel.  During the export, I create separate tabs/sheets for each of the 14 metrics and then copy the appropriate data into each sheet. 

    Problem - sometimes the data is copied and sometimes not.  If I have "On Error Resume Next" turned off, the error is in the "PasteSpecial" line

    xlApp.ActiveSheet.PasteSpecial Format:="Unicode Text"

    as stated, this is not consistent - sometimes it works and sometimes I get the error.

    If I have "On Error Resume Next" turned on, I don't get the error, but the data is not pasted.

    here is some of the code

    '===================== copy data to Excel =========================

    '===copy Logic
    xlApp.Sheets("Logic").Select
    xlApp.Range("A3") = "data goes here"
        'set filter
        FilterApply Name:="MissingLogic"
        'select data and count
        SelectTaskColumn Column:="ID"
        'On Error Resume Next
        linkscount = Application.ActiveSelection.Tasks.count
        'select data to be copied
        SelectTaskField Row:=0, Column:="ID", Width:=15, Height:=linkscount, Extend:=True
        Application.EditCopy
    If linkscount > 0 Then
        'select cell to paste data
        xlApp.Range("A3").Select
        'pastespecial Unicode Text
        xlApp.ActiveSheet.PasteSpecial Format:="Unicode Text"
    Else
        'if no data found, insert message
        xlApp.Range("A3") = "No data found"
    End If
    linkscount = 0

    '===copy Leads
    xlApp.Sheets("Leads").Select
    xlApp.Range("A3") = "data goes here"
        FilterApply Name:="Leads"
        SelectTaskColumn Column:="ID"
        'On Error Resume Next
        linkscount = Application.ActiveSelection.Tasks.count
        SelectTaskField Row:=0, Column:="ID", Width:=15, Height:=linkscount, Extend:=True
        Application.EditCopy
    If linkscount > 0 Then
        xlApp.Range("A3").Select
        xlApp.ActiveSheet.PasteSpecial Format:="Unicode Text"
    Else
        xlApp.Range("A3") = "No data found"
    End If
    linkscount = 0

    ================

    I have written a fair amount of VBA, but I have never seen this.  Anybody have a clue? 

    GEM

    Thursday, November 29, 2012 3:16 PM

All replies

  • The fact that it is intermittent would suggest that its caused by the contents of the clipboard that's causing the problem - maybe an invalid character being output in some scenarios and not others.

    Have you tried putting a break on the line that is causing the problem, then trying to do the paste-special manually (i.e. opening up the Excel app, then doing a paste special)? This might give you a more meaningful error message explaining what the actual problem is.

    If it won't paste into excel, try another app (Notepad/Word), to see if you can see what it is about the data that is stopping it pasting.

    Thanks,
    Andrew

    PS. I'm quite interested in the DCMA 14 point assessment, and would love to hear some real world experience of applying it to a set of schedules. Are you collating the results, or are schedulers just doing it on demand? Are you doing this regularly, and if so, at what frequency? Are you assessing all your schedules, or just a handpicked selection?

    Thursday, November 29, 2012 3:30 PM
  • Andrew,

    I should have mentioned that it copies and pastes just fine manually.  When the code stops, I can copy and paste just fine.  Also, if I step thru the program, everything seems to work just fine.  It's when the program runs normally that I get the intermittent behavior.

    IMHO, I find the DCMA 14 Point Metric only marginally useful.  The concept is fine but it has not been fully evaluated - example, the values appear to be arbitrary (missing logic > 5% is bad - maybe,  what if some of that missing logic should be on the CP?).  Also, there are some areas that are not evaluated - missing baselines, schedule margin, verticle integration, WBS integrity, IMP integrity (if IMS used), specific milestone performance etc.

    But because MDA is high on metrics - and this is one - I have to use it.

    This is mostly on demand, but done regularly with summaries presented during PMRs (Program Management Reviews).

    There are some COTS products out there that do this - SteelRay, Fuse etc.  This organization won't buy those due to budget constaints, so I built my own.

    GEM

    Thursday, November 29, 2012 3:54 PM
  • having stepped thru this a number of times, it seems that the issue is the paste function.  when i "editcopy" in Project, that normally makes the paste function available in Excel.  however, sometimes it does and other times it does not.  sometimes the paste function is highlighted - indicating it is available - but then it grays out - indicating it is not available simply by selecting the next code line "xlApp.Range("A3").Select"  -

    when that happens, nothing gets pasted.

    anybody know what would cause the paste function to activate/deactivate?

    GEM

    Thursday, November 29, 2012 7:42 PM
  • Problem solved -

    SelectTaskField Row:=0, Column:="ID", Width:=15, Height:=linkscount, Extend:=True

    is the offending code

    specifically Width:=15

    I have 15 columns - but the width includes the starting column (ID), thus I was asking for 16 which would sometimes return data that was unacceptable for pasting.  the width should be 14

    (the starting field (ID) is 1 plus 14 others to extend to a total of 15)

    now everything works as it should

    GEM

    Thursday, November 29, 2012 9:11 PM
  • GEM,

    Is there a particular reason you want to use foreground processing, (i.e. selecting data with copy and paste), instead of background processing, (i.e. operating directly on Project's objects to gather the data and writing directly into Excel's objects)? The latter is generally always faster and more efficient.

    Is there a reason you need unicode text? Unless there are some special characters in one of your fields (i.e. over and above normal ASCII), a regular text copy should work fine. I vaguely recall trying to work with unicode characters between Project and Excel in a macro I was writing several years ago and found that it just didn't seem to work properly. I never determined why.

    Have you considered either reading the Project data into a set of arrays and then dumping those into Excel? That's the method I use when exporting Project data to Excel. Or, you could simply read a Project field and write it directly into Excel on the fly.

    John

    Friday, November 30, 2012 2:19 AM
  • Glad you got it sorted, and thanks for the info on your use of the 14 point assessment.

    Friday, November 30, 2012 11:08 AM