Posts by Canuck

    Re: Interactive Pie Chart


    Quote from Badger101

    All possibble with VBA post a sample.


    What VBA code can you use to be able to click on a slice and have it show you the corresponding data?

    Re: Interactive Pie Chart


    Below is the code I'm using to get my data from MS Project into Excel. So far I've just been using some simple formulas to do a count of items within 3 different thresholds which is then plotted as a pie chart. What I need to do now is make the pie charts interactive so that when you click on a slice you see the data behind it.


    Code
    =COUNTIF(H:H,"<10")
    =COUNTIF(H:H,"<20")-L4
    =COUNTIF(H:H,">20")


    I'm trying to create a piechart to display the variance from project deadlines.


    Red > 10 days
    Yellow 5-10 days
    Green < 5 days


    I have the macro written and the piecharts are being created however they seem to lack functionality. I would like to be able to click on a segment of the chart and have it display the data (projects) that have that status. Any ideas on 3rd party software, JavaScript, any other methods of creating such an interactive pie chart?

    Re: Import Data from MS Project 2003


    Well without doing a conversion I get the following numbers:


    Raw data not dividing by 480:
    0 0
    40800 68640
    40800 47040
    47040 68640
    68640 68640


    Data in Project:


    0 days 0 days
    551 days 626 days
    551 days 564 days
    564 days 626 days
    626 days 626 days


    And looking at it closer they're not all off by the same factor which is really strange.

    Re: Import Data from MS Project 2003


    Data viewed in Project:
    0 0
    551 626
    551 564
    564 626
    626 626
    0 0


    Data imported into excel:
    0 0
    85 143
    85 98
    98 143
    143 143
    0 0


    I'm dividing the raw data by 480 because I read that the standard day in Project is 8 hours and the base unit is minutes therefore:


    8*60=480


    Maybe they are using a different base unit or length of day in this Project file. I know that they're all off by the same amount and I guess I can just divide by a different number to get the values to match but I'd like to figure out the root cause of the skewing.

    Re: Import Data from MS Project 2003


    thanks for the help! My macro seems to be pulling in the data except it doesn't match the data that I see in Project? When the Asgn.StartVariance or Asgn.DurationVariance are 0 it works fine but all other numbers are wrong. Any ideas or other tips on my code are appreciated.


    Re: Import Data from MS Project 2003


    Well I seem to have reversed the code and produced output however I still have a couple questions. Is there any way to open the file without actually displaying the project file on the screen. The problem is that this Project file is linked with several other files so when it opens it asks you whether you want to "open resource pools to see assignments across all sharer files". Eventually I want this macro to run on its own so I need to eliminate this user dependent behavior.


    Re: Import Data from MS Project 2003


    I want the macro to run from excel and import data from a project 2003 file. The reason I need the macro in excel is that the project file is a master schedule that is read-only. The code I've started with goes the other way and should be reversible. I really need to figure out the vba procedure to open and read from the project file.


    Code I'm working with:


    http://masamiki.com/project/export-hierarchy-to-excel.html

    I would like to write a macro which imports certain data from a MS Project 2003 file. I found a Project macro which exports to excel but nothing on importing through excel. The main thing is how to get the project file opened.


    Thanks in advance!


    -Canuck

    I'm working on a GUI that schedules a copy/paste macro to be run on a certain schedule. I'm using the ontime method but the macro/procedure I want it to run needs to take a couple parameters for where to copy and paste. The problem is that the ontime function wants a string for the procedure so my parameters don't get passed correctly.



    How can I get the parameters passed correctly?

    I have the following code:



    The problem is this line:


    Code
    Module1.StartTimer("hour", CopySheet, PasteSheet)


    I get a compile error expected =


    Help is appreciated! Thanks in advance!

    Re: Copy row every hour


    Actually one last question. My Stoptimer method doesn't seem to be stopping the timer. I have a call to stoptimer in the beforeclose for the workbook and also want to bind it to a button.




    Nevermind I think I answered my own question. I needed dTime to be a global variable.

    :thanx:

    Re: Copy row every hour


    Yeah I tried that without success. I'm thinking I need to change this line:


    Code
    wg.Range("A1:G1").PasteSpecial Paste:=xlPasteValues


    So that the range increments everytime. Something with a counter?


    Code
    wg.Range("A(1+i):G(1+i)").PasteSpecial Paster:=xlPasteValues
    i=i+1


    Something like that although that's not correct syntax.

    Re: Copy row every hour



    Well this is what I've optimized the macro to. Now I just was wondering how to make it paste to the next row each time rather then inserting a row and pasting at the top. (so that it is sorted oldest to newest)

    I have a worksheet that is being update with test data and every hour I want to copy a row of data from that worksheet to another. I was able to find the code to time it but I don't know how to copy it to another worksheet and how to move down a row each time in both sheets.


    This is what I have so far: