Work format problem for .csv export

K

Kila01

I have made the macro below where a custom filter a.o. produces a .csv file
to be imported in Siebel.
However, the Work column gives me problems. I cannot in MSP Options select a
"clean number" format instead of "hrs" or "h" which means when running the
Siebel Import afterwards, it fails, only straight number format recognized.

Right now I survive by opening the .cvs file and do a manual Search/Replace
- but when rolling out the solution to our units.......:)

One of three solutions needed:

1: Show me how to deselect this format in Project.
2: Modify the "Work" column format in the import macro, if possible
3:Or extend the MSP macro to open the .csv, do the search/replace and
Close.SaveChanges = True

MapEdit Name:="Export Tasks to Siebel", Create:=True,
OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True,
TableName:="Task_Table1", FieldName:="ID", ExternalFieldName:="Sorting",
ExportFilter:="Export_Filter_Siebel", ImportMethod:=0, HeaderRow:=True,
AssignmentData:=False, TextDelimiter:=",", TextFileOrigin:=0,
UseHtmlTemplate:=False, IncludeImage:=False
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text3", ExternalFieldName:="Project"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text1", ExternalFieldName:="Domain"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text2", ExternalFieldName:="Type"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Name", ExternalFieldName:="Name"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Work", ExternalFieldName:="Budgeted Hours"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Start", ExternalFieldName:="Planned Start"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Finish", ExternalFieldName:="Planned Completion"
FileSaveAs Name:="C:\Documents and Settings\All Users\Desktop\Task
Export File.csv", FormatID:="MSProject.CSV", map:="Export Tasks to Siebel"

MsgBox ("'Task Export File.csv' file saved on your Desktop")


Regards

Kim
 
J

JulieS

Hello Kim,

A quick work around is to modify the Project file by inserting one of
the spare text fields (it appears in your export that you are using
Text1, Text2, and Text3 already) and customizing the field with the
formula [Work]/60. That will just show the number of hours of work
without the "hr" following. Export that field instead of the Work
field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
 
K

Kila01

Hi Julie

I bow my head in respect - what a simplicity, what a beaty! The solution
works, thank you and regards from Copenhagen :)

Kim

"JulieS" skrev:
Hello Kim,

A quick work around is to modify the Project file by inserting one of
the spare text fields (it appears in your export that you are using
Text1, Text2, and Text3 already) and customizing the field with the
formula [Work]/60. That will just show the number of hours of work
without the "hr" following. Export that field instead of the Work
field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project


Kila01 said:
I have made the macro below where a custom filter a.o. produces a .csv
file
to be imported in Siebel.
However, the Work column gives me problems. I cannot in MSP Options
select a
"clean number" format instead of "hrs" or "h" which means when running
the
Siebel Import afterwards, it fails, only straight number format
recognized.

Right now I survive by opening the .cvs file and do a manual
Search/Replace
- but when rolling out the solution to our units.......:)

One of three solutions needed:

1: Show me how to deselect this format in Project.
2: Modify the "Work" column format in the import macro, if possible
3:Or extend the MSP macro to open the .csv, do the search/replace and
Close.SaveChanges = True

MapEdit Name:="Export Tasks to Siebel", Create:=True,
OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True,
TableName:="Task_Table1", FieldName:="ID",
ExternalFieldName:="Sorting",
ExportFilter:="Export_Filter_Siebel", ImportMethod:=0,
HeaderRow:=True,
AssignmentData:=False, TextDelimiter:=",", TextFileOrigin:=0,
UseHtmlTemplate:=False, IncludeImage:=False
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text3", ExternalFieldName:="Project"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text1", ExternalFieldName:="Domain"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text2", ExternalFieldName:="Type"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Name", ExternalFieldName:="Name"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Work", ExternalFieldName:="Budgeted Hours"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Start", ExternalFieldName:="Planned Start"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Finish", ExternalFieldName:="Planned Completion"
FileSaveAs Name:="C:\Documents and Settings\All Users\Desktop\Task
Export File.csv", FormatID:="MSProject.CSV", map:="Export Tasks to
Siebel"

MsgBox ("'Task Export File.csv' file saved on your Desktop")


Regards

Kim
Click to expand...
Click to expand...
 
J

JulieS

You're most welcome Kim and thanks for the lovely feedback.

Regards from beautiful coast of Maine.

Julie

Kila01 said:
Hi Julie

I bow my head in respect - what a simplicity, what a beaty! The
solution
works, thank you and regards from Copenhagen :)

Kim

"JulieS" skrev:
Hello Kim,

A quick work around is to modify the Project file by inserting one of
the spare text fields (it appears in your export that you are using
Text1, Text2, and Text3 already) and customizing the field with the
formula [Work]/60. That will just show the number of hours of work
without the "hr" following. Export that field instead of the Work
field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project


Kila01 said:
I have made the macro below where a custom filter a.o. produces a
.csv
file
to be imported in Siebel.
However, the Work column gives me problems. I cannot in MSP Options
select a
"clean number" format instead of "hrs" or "h" which means when
running
the
Siebel Import afterwards, it fails, only straight number format
recognized.

Right now I survive by opening the .cvs file and do a manual
Search/Replace
- but when rolling out the solution to our units.......:)

One of three solutions needed:

1: Show me how to deselect this format in Project.
2: Modify the "Work" column format in the import macro, if possible
3:Or extend the MSP macro to open the .csv, do the search/replace
and
Close.SaveChanges = True

MapEdit Name:="Export Tasks to Siebel", Create:=True,
OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True,
TableName:="Task_Table1", FieldName:="ID",
ExternalFieldName:="Sorting",
ExportFilter:="Export_Filter_Siebel", ImportMethod:=0,
HeaderRow:=True,
AssignmentData:=False, TextDelimiter:=",", TextFileOrigin:=0,
UseHtmlTemplate:=False, IncludeImage:=False
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text3", ExternalFieldName:="Project"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text1", ExternalFieldName:="Domain"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Text2", ExternalFieldName:="Type"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Name", ExternalFieldName:="Name"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Work", ExternalFieldName:="Budgeted Hours"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Start", ExternalFieldName:="Planned Start"
MapEdit Name:="Export Tasks to Siebel", DataCategory:=0,
FieldName:="Finish", ExternalFieldName:="Planned Completion"
FileSaveAs Name:="C:\Documents and Settings\All
Users\Desktop\Task
Export File.csv", FormatID:="MSProject.CSV", map:="Export Tasks to
Siebel"

MsgBox ("'Task Export File.csv' file saved on your Desktop")


Regards

Kim
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using ProjectAfterSave 0
Run-time error 1004 7
Controlling Microsoft Project from Excel 1
Opening an Excel Worksheet from Project yields an empty project... 90%of the time! 2
Sending the password in FileOpen 4
automatic arrangement in MS Project for imported Excel-lists 2
Passing password from Access 2
Project SummaryInfo overwritten when FileOpen executes. 1

Top