I would like to share a Excel macro which I have created for simplifying file import to Projectwise from XLS. This script allows to browse for folder and fill the file list in Excel spreadsheet.

How to use?

1. Enable macros in Excel

   

2. Open PW_Excel_import_script.xlsm
3. Click OK
4. 
5. Macro Main window opens
6. 
7. Choose "Browse" and select folder which to scan for source files for later importing in Projectwise. Note: the dialog will list only folders this is normal
8. Afterwards click "OK"
9. 
10. It will show a progress bar
11. 
12. Finished
13. 
14. Afterwards check the results and save file as legacy Excel 98-2003 format (XLS) 
15. Import in Projectwise using PW Excel Export-Import tool.

Options

After file list is loaded the columns will be filled with filename and path to source file. 2nd row is reserved for Excel formulas or default attribute values. This row will remain empty and is used to fill down the values/formula for other columns.

.

Values marked in green are mandatory for each row. If some files are not needed for import then one of these cells can be deleted and it will not import in Projectwise afterwards and also will be logged as an error in log file. Other cells can be filled manually.

It is possible to append more files to list by executing the macro again using icon in upper left corner:

To restart and clear sheet use other custom icon in upper left corner:

Custom attributes(environments)

Attached file contains only general attributes. To be able to fill your custom attributes also it is needed to copy these from your PW environments. Create a Excel template using PW Excel Export-Import tool and copy additional columns from created template and also replace Sheet2.

Known issues:

• Projectwise import tool only allows to select .XLS  files by default. If the file was saved using original .XLSM extension it is actually also possible to select  other Excel file versions if providing filename filter "*.XLSM" instead of filename.

• If using formulas for filling cells and formula returns error (#N/A,#VALUE, etc.) the PW Excel import tool will fail. So use IFERROR function in Excel so in case of error value an empty value is returned.
• Sometimes the macro doesn't auto-close when import tool opens the file and it can cause the import to fail. Not sure what causes this.
• Even that second row doesn't contain any values the import tool still logs this as an error. Either delete second row before importing or just ignore this warning in log.