0

When I export a Microsoft Project file into Excel using the export tool, the resulting XLSX file has 1 table called Task_Table1. Unfortunately that table only includes the header row. Is there any way to automate adding all non-empty rows into that table, or alternatively making Project include all the lines in the table it creates when exporting? I'm open to all options, including Excel script and power automate.

Background: I'm trying to feed the exported Excel into a Power Automate flow, but I need it to have a proper table in order to use the "get rows in table" step.

Improve this question
8
  • Are you using Microsoft Project's export tool accessed via the Visual Reports button on the Report tab?
    – Kenny Arnold
    Nov 4, 2021 at 23:52
  • @KennyArnold No, the "Save as" option and choosing XLSX and my map
    – trilo
    Nov 7, 2021 at 13:14
  • I'm not sure what you mean by "Unfortunately that table only includes the header row". Are you seeing any task data in this sheet aside from the field headers? Can you include a screenshot of what your spreadsheet looks like? I need to know in order to give an answer.
    – Kenny Arnold
    Nov 8, 2021 at 16:34
  • @KennyArnold The table in the generated excel has 0 rows, just headers. When i choose the table name in the top left side (Task_Table1), only the header is marked and the rest of the data lines arent included in any table. I've solved it by writing an excel automate script which retrieves the sheet, enumerates the tables, removes them (i dont need an empty header table), and adds a new table from the first row down to the last populated line. It works, but I'd avoid it if i could
    – trilo
    Nov 10, 2021 at 11:43
  • @trilo 1) If you have a working solution, post it as an answer and accept it so that others can benefit in the future. 2) I agree with Kenny--screenshots would be very helpful as I am unable to replicate the issue; the Save as Excel feature is robust and often used--perhaps consider re-installing the Project application.
    – Rachel Hettinger
    Nov 10, 2021 at 14:39

1 Answer 1

Reset to default
1

No solution so far from the community, so I did it dirty by running this script on the newly generated XLSX file:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheets()[0];
    let tablearr = selectedSheet.getTables();

    tablearr.forEach((currtable) => {
        currtable.convertToRange();
    });

    let newTable = workbook.addTable(selectedSheet.getRange("A1: O1").getExtendedRange(ExcelScript.KeyboardDirection.down), true);

    newTable.setName("Gant1");
}

Edit the column range according to your own solution

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.