Despite the tip, did you try not closing the source workbook before you pasted?
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Tuesday, March 14, 2017 3:23 PM To: profoxtech@leafe.com Subject: New Excel BREAKS Prior Working Automation!
Good afternoon fellow ProFoxers!
I hope you are faring well - for those of you like me in the NE USA hit by the storm. They sent out emergency communications yesterday from my job - saying office would be closed today - yet, instead of FEET of Snow - I woke up and all the Rain/sleet seems to have melted it down to like 3 or 4 inches tall at best! Well - alas - I digress.
So - here's the deal. Recently I had a User get a problem with a particular option in an application - something that had always worked. Stranger still - as the old saying goes "It works on MY Machine" is also occurring - since the problem would NOT occur on my machine. Even went a step farther, as he had the gal sitting next to him try the same option - and it failed for her. I then went farther. I logged into another machine, and it worked. Went to a QA gal - she tried it - and it work. In the end - a buddy of mine in Systems who deals with networking and user support - he had like 7 different users try it - and it failed for all of them. In the end - his deduction was problem being tied to Excel - as I am running 2010 - and the systems experiencing the problem are 2013.
Short explanation of the actual problem. This app - reads in an Excel file using Automation. Grabs the data in the header area of spreadsheet and fills in var's. Then it takes the main data - a bunch of rows/columns - and pushes the data into a CSV file that it creates via Automation. The 2nd phase shows a screen w/Header info - and displays name of the CSV file. Then asks if you want to Proceed with processing the data. Here in lies the problem. In my case - when I run the option - the CSV file is fine. But, for the users that are having the problem - the CSV file is EMPTY!
Now - before reporting this problem to you all here - I've been doing a bunch of research - as well as Testing various options. I even looked at a prior thread here in this forum - as it was VERY Similar to my own conundrum: "Copy Excel worksheet to another workbook". And, one of the tricks I noticed that was mentioned is that after Copying the range of Cells from the FROM Excel file - and before Pasting into the TO Workbook (that will become the CSV file) - that it was suggested to CLOSE the From Workbook. So - I did that - but, then I get the following error message being thrown: "The object invoked has disconnected from its clients."
I even tried a similar operation by hand (as I have remote access to another PC in my office that has Excel 2013 - so I can test for the problem) - by doing the Copy of the Cells, Closed the workbook - then went to the Other workbook and did Paste - and it worked - NO Error and data was pasted!
As such - I'm rather stumped! It's really SUCH a Drag when changes to Office now crash out Code that previously worked.
A fellow developer has suggested another Kludge - as Gene so adeptly states the term - which is to do a more manual pasting of cell data one by one - literally assigning the values to the Cells instead of by doing the Paste command. I did do a test by simply assigning "Hello World" to a single cell - and that DID work. But, I'd rather a more Elegant solution AND something that knows what the Actual problem is and can fix it with less code. Especially since I worry that this problem may run DEEPER than the users think - and that a BUNCH of other similar options in this application may ALSO Start to fail for all these users!
TIA!
Regards. Kurt Wendt Senior Systems Analyst