Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-16-2013, 06:25 AM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-16-2013, 08:01 AM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 12-16-2013, 08:18 AM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 12-16-2013, 10:13 AM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

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?
Reply With Quote
  #5  
Old 12-16-2013, 11:13 AM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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).
Reply With Quote
  #6  
Old 12-16-2013, 11:24 AM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

Hmmmm. When I paste "Keep Source Formatting" is the default and all works well.
Reply With Quote
  #7  
Old 12-16-2013, 12:33 PM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Julie
Please find herewith a sample for you to try your end.
Tony
Attached Files
File Type: zip Sample1.zip (51.9 KB, 5 views)
Reply With Quote
  #8  
Old 12-16-2013, 01:51 PM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

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.
Attached Files
File Type: zip Large Numbers in Pred.zip (173.6 KB, 9 views)
Reply With Quote
  #9  
Old 12-16-2013, 03:21 PM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 12-16-2013, 04:36 PM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

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.
Reply With Quote
  #11  
Old 12-17-2013, 01:20 AM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 12-17-2013, 07:07 AM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

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.
Reply With Quote
  #13  
Old 12-17-2013, 01:35 PM
OTPM OTPM is offline Copying data from Project to Excel Windows 7 32bit Copying data from Project to Excel Office 2010 32bit
Expert
Copying data from Project to Excel
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Julie
Many thanks for your help.
Tony
Reply With Quote
  #14  
Old 12-20-2013, 10:25 AM
JulieS JulieS is offline Copying data from Project to Excel Windows 7 64bit Copying data from Project to Excel Office 2010 32bit
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
JulieS will become famous soon enough
Default

Sorry we couldn't figure out what was going on......
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying data from Project to Excel Copying data from sheet with deleted columns creates blanks ZGreyArea Excel 1 11-20-2013 10:12 AM
Copying data from Project to Excel Push data automatically from Acess or Excel to create a Gantt chart in Project GST2212 Project 3 07-06-2012 12:52 PM
Copying data from Project to Excel Copying and pasting from Excel Lorna B Word 1 03-20-2012 11:58 PM
Copying data from Project to Excel Copying data from one cell to another automatically mrphilk Excel 4 06-10-2010 11:52 PM
Copying data from Project to Excel Copying text from Excel to Word Fossils13 Office 1 02-22-2010 08:39 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft