none
Auto open projects for export to excel RRS feed

  • Question

  • Hello every one,

    I'm new with VBA but I was working with some kind of macro that automatically opens the project and export it to excel. So far, I can do the export by manually opening the project but since I'm new to VBA I am having troubles working on the automation of project opening. 

    Here's how my code looks so far for exporting:

    Sub Export()

    FileSaveAs Name:="mytargetfolder" & ActiveProject.Task(1).Project &".xlsx", FormatID:="MsProject.ACE", map:="ASM1"

    FileCloseEx Save:=pjDoNotSave, Checkin:=True

    End Sub

    Anyone who can help on how can I open the projects that I need to export automatically? If you had a source code that would be helpful too. I also accept any helpful criticism. Thank you!

    Friday, September 25, 2020 6:02 PM

Answers

  • josh_1026,

    Try this. Based on what you've told me, it should do what you need.

    John

    Sub ExportMSFiles()
    'This macro finds all Project files (with a specific name suffix) located on a remote server
    '   and saves each as an Excel Workbook using a pre-formatted export map
    'Written by John - Project 9/26/20
    Dim prj As Object
    Dim fs As Object, f As Object

    Set fs = CreateObject("scripting.filesystemobject")
    Set f = fs.getfolder("remote server path")  'for example "\\" or "<>\"

    For Each prj In f.Files
        'search for Project files with an "_MS" suffix on the name
        If InStr(1, prj.Name, "_MS.mpp") <> 0 Then
            FileSaveAs Name:="desired path for Excel Workbook" & prj.Name & ".xlsx", FormatID:="MSProject.ACE", Map:="ASM1"
        End If
    Next prj
    End Sub

    • Marked as answer by Josh_1026 Monday, September 28, 2020 8:55 PM
    Saturday, September 26, 2020 10:01 PM

All replies

  • josh_1026,

    Tell me more about your "automatically" opening a project file. What does "automatically" mean? Will your macro reside in Project, Excel, or somewhere else?

    If you need to open and export a series of Project files to a single Excel Workbook, I'd have the code reside in Excel, but if you have multiple Project files that you want to export to multiple individual Excel Workbooks then I suggest a single Project file that contains the names (or path) of each of the individual Project files (I used that method recently for another user).

    Also, I see you are exporting with a map. Export maps are limited in what they can do, when I need to get Project data into Excel, I export specific fields, including timescale data if needed.

    So, tell me more and let's see what we can do. What is your end goal?

    And for reference, what version of Project are you using? What version of Office?

    John

    Friday, September 25, 2020 6:47 PM
  • Thanks John,

    Apologies if its quite unclear. But here are my answers:

    What does "automatically" mean? Will your macro reside in Project, Excel, or somewhere else?

    • The scenario is like, open one project > export using the map> move to the next project.

    multiple Project files that you want to export to multiple individual Excel Workbooks then I suggest a single Project file that contains the names (or path) of each of the individual Project files (I used that method recently for another user).

    • This sounds like what I want to happen :) so like 1 project = 1 excel

    Also, I see you are exporting with a map. Export maps are limited in what they can do, when I need to get Project data into Excel, I export specific fields, including timescale data if needed.

    • I'm not sure about the problems that I might have here but yeah It works well so far. 

    And for reference, what version of Project are you using? What version of Office?

    • I'm using Office 365 and Project Professional online desktop client MSO.
    Friday, September 25, 2020 8:59 PM
  • Josh_1026,

    You still didn't quite answer the "automatically" part. Where are the project files stored (i.e. on your local PC drive, a common server, etc.)?

    Do this. Open the VB Editor window and then open an Immediate window. Now open one of the Project files and type this into the VBE Immediate window:

    Print ActiveProject.FullName

    What does it show?

    You also didn't answer my question about your end goal. What exactly are you trying to do? That will help to determine if an export map will do what you need in the end or if you need to go to the next level (i.e. custom export, not a map).

    For reference I can help you with this over the weekend.

    John

    Friday, September 25, 2020 10:35 PM
  • John,

    Really appreciate your help. Here's my response. 

    You still didn't quite answer the "automatically" part. Where are the project files stored (i.e. on your local PC drive, a common server, etc.)?

    • This is stored on a server. An enterprise one.

    Do this. Open the VB Editor window and then open an Immediate window. Now open one of the Project files and type this into the VBE Immediate window:

    Print ActiveProject.FullName

    What does it show?

    • It shows the project file name which is "2017_CRM_MS".

    You also didn't answer my question about your end goal. What exactly are you trying to do? That will help to determine if an export map will do what you need in the end or if you need to go to the next level (i.e. custom export, not a map).

    • Basically, the goal is to open each projects that i need from the server and export them one by one to excel. I guess the mapping wouldn't be an issue since from the data I exported all looks okay :) but if custom export requires it, that would be okay too.

    Thank you!


    Saturday, September 26, 2020 12:52 AM
  • josh_1026,

    Sorry but you saw more than just the file name in the Immediate window. There would have been a full path shown with the file name at the end. I ask questions for a reason.

    1. How many Project files do you need to export?

    2. Do you want to export all the Project files on the server or only a selected set?

    3. If a selected set, is there anything unique about the set?

    4. Do the file names change and if so how do they change?

    5. Do you need to do this on a regular basis (e.g. once a week)?

    John


    Saturday, September 26, 2020 1:18 AM
  • John,

    Oh i see, there's no path name that I can see when I typed the command. ony the "<>\" before the project name.

    1. How many Project files do you need to export?

    • Not sure about the exact number but roughly around 70-80 workplans.

    2. Do you want to export all the Project files on the server or only a selected set?

    • Only a selected set, correct.

    3. If a selected set, is there anything unique about the set?

    • Like my example 2017_CRM_MS, The naming format goes like YYYY_XXXXX_MS. I can say that all of them ends with MS.

    4. Do the file names change and if so how do they change?

    • File names doesn't change. But there are file names that goes like 2017_CRM_2_MS, if we think there are too many task already on the first one. We're creating a new one not renaming them. Sorry if its sounds confusing not a native English speaker. 

    5. Do you need to do this on a regular basis (e.g. once a week)?

    • Once a week, yes.

    Saturday, September 26, 2020 2:19 AM
  • josh_1026,

    Okay, give me some time to develop some code that should get you headed in the right direction. It might be later today (Saturday) or tomorrow.

    John

    Saturday, September 26, 2020 5:22 PM
  • John,

    Thank you for your help :) It would really be a great help.

    Saturday, September 26, 2020 7:21 PM
  • josh_1026,

    Try this. Based on what you've told me, it should do what you need.

    John

    Sub ExportMSFiles()
    'This macro finds all Project files (with a specific name suffix) located on a remote server
    '   and saves each as an Excel Workbook using a pre-formatted export map
    'Written by John - Project 9/26/20
    Dim prj As Object
    Dim fs As Object, f As Object

    Set fs = CreateObject("scripting.filesystemobject")
    Set f = fs.getfolder("remote server path")  'for example "\\" or "<>\"

    For Each prj In f.Files
        'search for Project files with an "_MS" suffix on the name
        If InStr(1, prj.Name, "_MS.mpp") <> 0 Then
            FileSaveAs Name:="desired path for Excel Workbook" & prj.Name & ".xlsx", FormatID:="MSProject.ACE", Map:="ASM1"
        End If
    Next prj
    End Sub

    • Marked as answer by Josh_1026 Monday, September 28, 2020 8:55 PM
    Saturday, September 26, 2020 10:01 PM
  • John, 

    Thank you for this. I'll work on this code and see what happens. I'll give an update if its a success. Thanks for all the help! 

    Sunday, September 27, 2020 10:30 PM
  • josh_1026,

    You're welcome and thanks for the feedback. Please give a update whether it works the first time or not. And, if you feel I answered you initial question, please consider marking my response with the code as the answer.

    John

    Monday, September 28, 2020 12:16 AM
  • John, 

    Just have concerns. I was trying to figure out where can I find the target folder. I looks like "<>\" doesn't work on this line? 

    Set f = fs.getfolder("remote server path")  'for example "\\" or "<>\"

    I'm not that sure if there's other way of getting the file path. 

    Monday, September 28, 2020 12:59 PM
  • josh_1026,

    When you say it doesn't work, do you get an error message?

    The path is what you see in the Immediate Window when you open one of the Project files stored on the server. On my system this is what I get. In my example the Project file (blocked out part) is buried a few levels down (i.e. JOHN-PC\Users\Guest\Active Jobs\) so that would be my "path"

    Show me a screen shot of what you see when you open one of the Project files on the server.

    John

    Monday, September 28, 2020 3:32 PM
  • John,

    I'm having trouble uploading the photo. but it goes like this, just like before.

    Print ActiveProject.FullName

    <>\2017_Analytics_MS.  

    I tried to rightclick the project file and hit Open file location but gives me an error.

    "We can't reach that folder right now. It's possible you're not connected to it or the folder no longer exist."

    I'm not sure if its because this is corporate ? 

    Monday, September 28, 2020 6:01 PM
  • josh_1026,

    When you run the macro with this line, what happens? Note, the path is a string so make sure you include the quotation marks.

    Set f = fs.getfolder("<>\")

    John

    Monday, September 28, 2020 6:16 PM
  • John,

    Yup I did include those quotation marks.

    It goes Runtime error '76':

    Path not found. 

    Monday, September 28, 2020 6:27 PM
  • josh_1026,

    I just noticed something, you said the Immediate Window showed

    <>\2017_Analytics_MS.

    Is that all it shows? It should show

    <>\2017_Analytics_MS.mpp

    Try this. Open a file that is on your local "C\" drive, either a Project or Excel file. Open the VB Editor and the Immediate Window. If it's a Project file use:

    Print ActiveProject.FullName

    If it's an Excel file use:

    Print ActiveWorkbook.FullName

    Now copy the file path (i.e. up to the file name) and insert it into the Set f = fs.getfolder() statement.

    Run the macro. What happens?

    John

    Monday, September 28, 2020 7:49 PM
  • John,

    Yes, it only shows <>\2017_Analytics_MS.

    I did create a locally saved project file on C drive and found the path. So, I did the statement Set f = fs.getfolder("C:\") and yeah, it did work. the export worked. I guess what's left for me now is to know the path for the server one. Any clues?

    Monday, September 28, 2020 8:44 PM
  • josh_1026,

    Instead of using "<>\" for the server path, try using "\\". Does that work?

    John

    Monday, September 28, 2020 8:57 PM
  • John,

    It did work but something weird happened. I've got few observations 

    • The macro runs if the project file is opened only. I think it wont run on multi projects like the plan, if none of them opens automatically from the desktop app.
    • I tried to open the project file from the server to test "\\". It did work, the data is there. however the file name that was saved was from the local project file name that I created earlier. Again, it supposed to export many, but since I only opened 1, it only extracted one. I think the mapping only does it job from there(aside from renaming the file based on the opened project file.
    Monday, September 28, 2020 9:16 PM
  • josh_1026,

    So are you saying that it runs (i.e. no errors), but it doesn't save any files, unless they just happen to be on your local "C:\" drive? What happens if close all open Project files, create a new blank project file and run the macro from there?

    The macro as written doesn't actually open any of the files, it just identifies them and then does a save as an Excel Workbook. Perhaps in your scenario, we need to actually open the file, do the save as an Excel Workbook and then close the file and move on to the next one.

    John

    Monday, September 28, 2020 9:40 PM
  • John,

    Does it sound like its just the same with my previous codes where I open one by one and run the macro to export it to excel? Is it still possible to do a macro that actually opens the project file then export it to excel?

    Tuesday, September 29, 2020 2:39 PM
  • josh_1026,

    Okay, let me tweak the code to do the open, export, close process. I'll get back to you.

    John

    Tuesday, September 29, 2020 3:31 PM
  • Thank you so much
    Tuesday, September 29, 2020 5:49 PM
  • josh_1026,

    This forum thread is getting rather long. Perhaps it would be more convenient for us to work one-on-one via e-mail. Contact me at the address below, I will ask some questions.

    Meanwhile, with no Project files open (other than a blank file), open the VB Editor and display the macro code in the window. Use the F8 key to step through the code. As you step through each line will be highlighted. When you get to the line highlighted below, hover your mouse over the item shown. What appears? (If hovering doesn't work, type Print prj in the Immediate Window.)

    John

    jmacprojataticlouddotdotcom

    (remove obvious redundancies)

    Tuesday, September 29, 2020 6:19 PM