20 hours nonstop searching for VBA answers! Looking for Excel Commands to execute from within an MS Project Macro RRS feed

  • General discussion

  • I need to automate copying data ranges from Project into Excel and have hit a brick wall!

    I have a macro which opens an Excel workbook and a Project file, sets the views, timescale, filters, etc.

    It also selects the first Project data range and copies it to the clipboard.

    Then manually, I

    1. flip to the open Excel workbook
    2. select the worksheet associated with that project
    3. paste the project data into the worksheet <CTRL-V>
    4. flip back to Project
    5. Select the next data section to copy <CTRL-C>
    6. flip  back to Excel
    7. go to another section of the worksheet
    8. paste the other project data into the worksheet <CTRL-V>

    I then flip back to Project and invoke the macro to open the next project file, set the views, etc.. and do the manual stuff again until all the project files have been processed.

    I need to automate the manual steps.... unfortunately, I get stuck on the paste command (which I thought was xlApp.Paste) and then flipping back and forth between project and Excel as needed.   The Project Excel export function doesn't give me all the info I need.

    Can anyone help me with the proper excel commands to use in the Project VBA macro?

    Thanks in advance.


    Sunday, February 12, 2017 11:12 AM

All replies

  • SiN,
    re: running Excel from MS Project

    Please note that I have never used MS Project.

    Some basics...
    When manipulating one application from another application, you must establish an object reference to the other application.
    You must tell VBA what you mean when you refer to xlApp or objApp or sludge or anything else you refer to.  Everything must be identified.

    There can be multiple versions of Excel open or none open. 
    Also, xl2013 and xl2016 opens each workbook iin its own window, each with its own Ribbon (toolbars). 
    Since you will? have an existing instance of Excel open, you should  be able to get (find) the existing Excel app by using the "GetObject" function.
    If that function in n/a in MS Project, you will have to look for its equivalent. 

    The VBA code could look like...
    Dim objXL As Object
    Dim WB As Object

    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")   ' note the comma
    On Error GoTo 0
    If objXL Is Nothing Then
      MsgBox "No Excel Application"
      Exit Function ' or Sub
      On Error Resume Next
      Set WB = objXL.Workbooks("StevesWorkbookName")    'use correct name
      On Error GoTo 0
      If WB Is Nothing Then
        MsgBox "Can't find workbook"
        Exit Function ' or Sub
      ' You now have a reference to the Excel Application and to the Excel Workbook.
      ' You also have to specify the worksheet and the cell where the pasting is to occur, such as...
      ' objXL.WB.Worksheets(2).Range("B3")
     End If

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, February 12, 2017 5:05 PM
    Sunday, February 12, 2017 4:53 PM