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
One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.com
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
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
RK - of course! That's what the original code essentially does!
So - here is a cut down version of the original code (yeah - I really should have posted this initially, was going to do so - but I forgot to include it!): oWorkBook.Activate rangeA = '"A7:D'+ALLTRIM(STR(lastrow))+'"' oRange = oExcel.ActiveSheet.RANGE(&rangeA) oRange.SELECT oRange.COPY()
oWorkBook2.Activate oExcel.ActiveSheet.Paste
jdate=ALLTRIM(STR(ROUND(julian(DTOC(DATE())),0))) mCSVname = csvpath+'BO'+jdate mCSVname = FORCEEXT(mCSVname,"csv")
oExcel.ActiveWorkBook.SAVEAS (mCSVname,xlCSV) oExcel.ActiveWorkBook.CLOSE(.F.) && the .F. parameter will prevent the dialogue box
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Tuesday, March 14, 2017 3:27 PM To: profoxtech@leafe.com Subject: RE: New Excel BREAKS Prior Working Automation!
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
[excessive quoting removed by server]
I suggest you put an oExcel.DisplayAlerts = .F. right after you instantiate the oExcel object (if you don't have it already).
Also, as a testing idea, you could do an oExcel.Visible right after the .Paste (and comment out the rest of the code) to see if that step worked. Of course that means you kinda need to be on a machine where it is failing.
Good ol' MS "Where do you want your problem today?"
-Charlie
On 3/14/2017 3:40 PM, Kurt Wendt wrote:
RK - of course! That's what the original code essentially does!
So - here is a cut down version of the original code (yeah - I really should have posted this initially, was going to do so - but I forgot to include it!): oWorkBook.Activate rangeA = '"A7:D'+ALLTRIM(STR(lastrow))+'"' oRange = oExcel.ActiveSheet.RANGE(&rangeA) oRange.SELECT oRange.COPY()
oWorkBook2.Activate oExcel.ActiveSheet.Paste
jdate=ALLTRIM(STR(ROUND(julian(DTOC(DATE())),0))) mCSVname = csvpath+'BO'+jdate mCSVname = FORCEEXT(mCSVname,"csv")
oExcel.ActiveWorkBook.SAVEAS (mCSVname,xlCSV) oExcel.ActiveWorkBook.CLOSE(.F.) && the .F. parameter will prevent the dialogue box
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Tuesday, March 14, 2017 3:27 PM To: profoxtech@leafe.com Subject: RE: New Excel BREAKS Prior Working Automation!
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
[excessive quoting removed by server]
OK - since you DID ask. FYI - I actually Step thru the code - and when I do - everything works fine! That's why its an even stranger conundrum!!!
Also - as previously mentioned - I actually Was testing on a machine that has the problem. And, even on there - when stepping thru the code - the problem then does NOT occur...
:-(
-K-
P.S. I will be essentially offline for next 2 days - am attending a 3D Printing conference in NYC and not going to work. Assuming its not postponed ANOTHER day due to the stupid weather!
On 3/14/2017 6:56 PM, Charlie wrote:
I suggest you put an oExcel.DisplayAlerts = .F. right after you instantiate the oExcel object (if you don't have it already).
Also, as a testing idea, you could do an oExcel.Visible right after the .Paste (and comment out the rest of the code) to see if that step worked. Of course that means you kinda need to be on a machine where it is failing.
Good ol' MS "Where do you want your problem today?"
-Charlie
On 3/14/2017 3:40 PM, Kurt Wendt wrote:
RK - of course! That's what the original code essentially does!
So - here is a cut down version of the original code (yeah - I really should have posted this initially, was going to do so - but I forgot to include it!): oWorkBook.Activate rangeA = '"A7:D'+ALLTRIM(STR(lastrow))+'"' oRange = oExcel.ActiveSheet.RANGE(&rangeA) oRange.SELECT oRange.COPY()
oWorkBook2.Activate oExcel.ActiveSheet.Paste jdate=ALLTRIM(STR(ROUND(julian(DTOC(DATE())),0))) mCSVname = csvpath+'BO'+jdate mCSVname = FORCEEXT(mCSVname,"csv") oExcel.ActiveWorkBook.SAVEAS (mCSVname,xlCSV) oExcel.ActiveWorkBook.CLOSE(.F.) && the .F. parameter willprevent the dialogue box
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Tuesday, March 14, 2017 3:27 PM To: profoxtech@leafe.com Subject: RE: New Excel BREAKS Prior Working Automation!
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
[excessive quoting removed by server]
Maybe try making sure you have disposed of your Excel object before attempting to open the CSV.
Or maybe sleep the VFP thread for a couple of seconds to give things a chance to flush.
declare integer sleep in "kernel32" long dwmilliseconds sleep(2000)
I assume you have Excel hidden during this. Maybe introduce a flag that will make it visible during this process when run onsite so you can see if it is calving at a particular point for the users.
-
Alan Bourke -
Charlie -
Kurt at VR-FX -
Kurt Wendt -
Richard Kaye