#1
|
|||
|
|||
Copying data from Project to Excel
Hi All I have a macro which copies data from MS Project to Excel. However with one of the fields Excel tries to guess what the format should be for the data being pasted. For example one field in Project looks like: 2,23,234,345,456,789,678,898,999 but when it is pasted in Excel in converts to: 2,23,234,345,456,789,000,000,000 Has anyone else had this issue and if so how did you overcome it? Tony |
#2
|
|||
|
|||
I'm guessing the long string of numbers is the predecessor or successor field? When I do a straight copy/paste (no code) from Project 2010 to Excel 2010 I am not seeing that issue. Excel treats the entry as "general" and not a number format.
What version of Project and Excel? |
#3
|
|||
|
|||
Hi Julie
Yes they are Predecessor task numbers. I have Project 2010 and Excel 2010. I have tried formatting the cells in Excel to General BEFORE pasting the data from Excel but to no avail. Excel still tries to convert the Predecessor data. When I do a "Save As" from Project to an Excel file it works perfectly. However as I have a template that I need to use to populate I need to be able to copy from Project then activate the Excel Workbook and then paste the data from A1. Hope this makes it clear what I need. Kind regards Tony |
#4
|
|||
|
|||
What happens if you do a straight copy/paste? If a copy/paste works without the code - my only suggestion is to check the code and see if it is throwing some sort of error.
I am also to SP-2 in Project and Excel - you? |
#5
|
|||
|
|||
Hi Julie
I am also on SP-2. I have formatted all cells on the worksheet to General. Then copied from Project and pasted in Excel using the "Match Destination Formatting" option but still get the same problem. I would love to see the code that Project uses when you export to Excel as that works perfectly :0). |
#6
|
|||
|
|||
Hmmmm. When I paste "Keep Source Formatting" is the default and all works well.
|
#7
|
|||
|
|||
Hi Julie
Please find herewith a sample for you to try your end. Tony |
#8
|
|||
|
|||
Well, there is something passing strange in your file - but I would expect some oddness as you have large numbers of blank rows. I imagine this is solely for testing?
Try the attached file. I have added a number of predecessors with high numbers but have left no tasks blank. |
#9
|
|||
|
|||
Hi Julie
What do you mean by "no blank tasks"? I have copied your plans data and it works perfectly!!!!!. I tried to send you a copy of the actual plan I am testing with but it is too big. Tony |
#10
|
|||
|
|||
When I opened your file I saw Task 1 with data and then nothing but empty task lines (with IDs) until task 1157. Why no data until line 1157? Those "blank" lines are entries in the database so they are not truly empty to Project.
|
#11
|
|||
|
|||
Hi Julie
I have just created a dummy plan, with 20 Predecessors on one task, and it works fine as you say!!!!! However I have also tried it on a completely different mpp file and intermittently it has the same issue of converting some Predecessor fields to numbers :0(. As explained previously when I do a Save As to Excel it works perfectly - it doesn't make sense. I don't suppose you have a copy of that code or know where I could get it from do you? Many thanks for your help so far. Tony |
#12
|
|||
|
|||
Hi Tony,
Sorry, I don't know the code used for the export to Excel. If the Save As works for you consistently can you re-write your code to call to that command instead of copy/paste? I'm not sure why you are experiencing intermittent hiccups on the copy/paste. There are several "don'ts" in Project - one is blank rows - my concern is especially if you are developing the code for other users, it is tough to control what they may have done in the Project file. The export to Excel just drops the data and may be more predictable. |
#13
|
|||
|
|||
Hi Julie
Many thanks for your help. Tony |
#14
|
|||
|
|||
Sorry we couldn't figure out what was going on......
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copying data from sheet with deleted columns creates blanks | ZGreyArea | Excel | 1 | 11-20-2013 10:12 AM |
Push data automatically from Acess or Excel to create a Gantt chart in Project | GST2212 | Project | 3 | 07-06-2012 12:52 PM |
Copying and pasting from Excel | Lorna B | Word | 1 | 03-20-2012 11:58 PM |
Copying data from one cell to another automatically | mrphilk | Excel | 4 | 06-10-2010 11:52 PM |
Copying text from Excel to Word | Fossils13 | Office | 1 | 02-22-2010 08:39 PM |