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]