none
Excel to Project export through VBA is slow. RRS feed

  • Question

  • Hi All,

    I did some VBA coding in Project to import tasks from excel sheet. In project the tasks were added like

    ThisProject.Tasks.Add("task1")

    I am using the same logic in excel to export the tasks like

    Set appProj = CreateObject("Msproject.Application")
    
    With appProj
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = pjManual
    End With
    
    appProj.FileOpen "C:\ProjectTemplate.mpp"
    
        Set aProg = appProj.ActiveProject
        appProj.Visible = False
    
    Set Task1 = aProg.Tasks.Add("Task ABC")
                
    With Task1
         .Rollup = True
         .OutlineLevel = 1
    End With

    But the export from excel to project is taking too much time. I tried disabling screenupdating and auto calculation but it didnt help.

    Is there anyway I can improve the speed?

    In both the cases I am taking the excel sheet values as array. And I am not directly reading from the sheet.

    Regards,

    Becks23



    • Edited by becks23 Wednesday, July 29, 2015 1:03 PM
    Wednesday, July 29, 2015 12:53 PM

Answers

  • The fastest solution will have a VBA macro in Excel collecting data then handing over to a Project macro to write data.

    There is another solution and that is for Excel to write to SQL Server and for Project to read from SQL Server. However I think the slowest part is Project creating and then writing all data for each new task, even with calculation off. But using SQL Server means the two files don't have to be open at the same time and so no confusion as to which is the newest file, the db only has one copy!

    I suspect though that the simplest solution of passing an array for each task with a macro in Excel and Project.

    Most robust and easiest to maintain is one macro, probably in Project as that is where the data ends up and is the App with the most work to do.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by becks23 Friday, August 7, 2015 5:54 AM
    Friday, July 31, 2015 9:53 PM
    Moderator
  • Hi Rod,

    Thanks for the solution.

    I did something like this before reading your reply.The below code is in excel.I am creating a project object in excel and trigerring the project macro and passing the required variable to it.It running like before and speed is same as before.I got the output which I required. Occasionally if I move away from excel then I get this error "Microsoft Office Excel is waiting for another application to complete an OLE action" other than this I dont have other problems.

    appProj.Application.Run "ImportfromExcel", array1, array2, array3, array4, array5, ws

    But the above line works good.

    Thanks for all the replies Rod/John.

    • Marked as answer by becks23 Friday, August 7, 2015 5:54 AM
    Friday, August 7, 2015 5:53 AM

All replies

  • becks23,

    I'm not sure what you define as "taking too much time" but when I run your macro from Excel, the first run takes .6679 seconds and subsequent runs take a mere .046875 sec. I did change the location of the template file from being directly at "C:" drive level to "C:\Users\John\Documents\ProjectTemplate.mpp" (it wouldn't allow me to save directly at "C:" level.

    Just for reference when I export data from Project to Excel I typically store the Project data in arrays before I even open Excel. Then I open Excel and dump the arrays. That keeps the system from having to jump between applications as is the case with a direct read (Project)/write (Excel). The same should apply going the other way.

    How large is your Excel file (i.e. rows/columns)?

    John

    Wednesday, July 29, 2015 8:22 PM
  • I've been doing a fair bit of importing Excel data to project macros recently and they can be slow for 1000,s of tasks. The slow bit is working between processes. So exporting from Project to Excel using arrays and Project VBA code is fast. Reading data from Excel into an array in Project VBA has no speed advantage because the data crosses from the Excel process to Project process so no speed gain.

    The only thing that will work is to have an Excel macro in the Excel file build a string for a task and call it from Project using:

    Sub Testme()

    dim str as String
    Dim xlApp As New Excel.Application
        Set xlApp = GetObject(, "Excel.Application")
        str = xlApp.Run("Test", 2)
        Set xlApp = Nothing
    End Sub

    "Test" is the name of the Function in Excel and 2 is the row number to read from. I haven't been able to read an array but the Function can return a comma delimted string and you can use Split() to convert to an array for writing into appropriate Project fields.

    This greatly reduces inter-process actions so speeding up the code. However it does require using a workbook with the required macro in it every time.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, July 30, 2015 11:26 PM
    Moderator
  • Rod,

    I probably wasn't clear enough in my second paragraph. When I said "the same should apply going the other way", I meant that starting with an Excel macro, it would likely be faster to read the Excel data into arrays, then open Project and dump the arrays. I think you are simply confirming that indeed is the case.

    John


    • Edited by John - Project Friday, July 31, 2015 2:51 AM clarification
    Thursday, July 30, 2015 11:53 PM

  • I probably didn't read it carefully enough! But yes macros at both ends can produce some very workable solutions.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, July 31, 2015 4:16 AM
    Moderator
  • Thanks John/Rod,

    The time taken is like (for 1058 line items in Project) when -

    Script is running in Project (tasks are read from excel sheets into an array) - 19 seconds.

    Script is running in Excel (Project file is set a MsProject.Application) - 3 mins 20 seconds.

    Fields that are updated in Project include start and finish dates and oddly some 50 custom fields (like flag/text/date).

    Regards,

    Becks23

    Friday, July 31, 2015 5:57 AM
  • becks23,

    You're welcome and thanks for the feedback.

    If you're able to run a macro from Project to read the data from Excel for over 1000 tasks and 50+ fields in 19 seconds, I'd say that's pretty good. I assume going the other way, (i.e. macro running in Excel), is not first loading the data into arrays before dumping into Project and that's why it takes over 3 minutes to execute. That supports the gain claims of using arrays as intermediate storage of data for large transfers.

    I've written a lot a complex data transfer macros over the years (99% are from Project to Excel) and I have always used arrays. I've never experienced an unacceptable run time although things did get slower with the release of Project 2010, I assume the slowdown is due to more overhead in the application and different file format. Rod may have more insight on that.

    Just for reference, something else I avoid for faster execution is doing everything possible in background mode (i.e. not working on selected objects in the view but working directly on objects in Project's underlying database). However, in a few cases, foreground processing is actually faster so my macros may jump from foreground processing to background processing to achiever optimum speed.

    Hope this helps.

    John

    Friday, July 31, 2015 3:56 PM
  • The fastest solution will have a VBA macro in Excel collecting data then handing over to a Project macro to write data.

    There is another solution and that is for Excel to write to SQL Server and for Project to read from SQL Server. However I think the slowest part is Project creating and then writing all data for each new task, even with calculation off. But using SQL Server means the two files don't have to be open at the same time and so no confusion as to which is the newest file, the db only has one copy!

    I suspect though that the simplest solution of passing an array for each task with a macro in Excel and Project.

    Most robust and easiest to maintain is one macro, probably in Project as that is where the data ends up and is the App with the most work to do.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by becks23 Friday, August 7, 2015 5:54 AM
    Friday, July 31, 2015 9:53 PM
    Moderator
  • Hi Rod,

    Can you tell me how can I do this

    "The fastest solution will have a VBA macro in Excel collecting data then handing over to a Project macro to write data."

    with a sample code.

    Regards,

    Becks23



    • Edited by becks23 Monday, August 3, 2015 10:55 AM
    Monday, August 3, 2015 6:54 AM
  • As explained in earlier post:

    Sub Testme()

    dim str as String
    Dim xlApp As New Excel.Application
        Set xlApp = GetObject(, "Excel.Application")
        str = xlApp.Run("Test", 2)
        Set xlApp = Nothing
    End Sub

    "Test" is the name of the Function in Excel and 2 is the row number to read from. I haven't been able to read an array but the Function can return a comma delimited string and you can use Split() to convert to an array for writing into appropriate Project fields.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, August 3, 2015 8:50 PM
    Moderator
  • Hi Rod,

    Thanks for the solution.

    I did something like this before reading your reply.The below code is in excel.I am creating a project object in excel and trigerring the project macro and passing the required variable to it.It running like before and speed is same as before.I got the output which I required. Occasionally if I move away from excel then I get this error "Microsoft Office Excel is waiting for another application to complete an OLE action" other than this I dont have other problems.

    appProj.Application.Run "ImportfromExcel", array1, array2, array3, array4, array5, ws

    But the above line works good.

    Thanks for all the replies Rod/John.

    • Marked as answer by becks23 Friday, August 7, 2015 5:54 AM
    Friday, August 7, 2015 5:53 AM