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 will
prevent the dialogue box
Regards,
Kurt Wendt
Senior Systems Analyst
Tel. +1-212-747-9100
www.GlobeTax.com
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of
Richard Kaye
Sent: Tuesday, March 14, 2017 3:27 PM
To: [email protected]
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:[email protected]] On Behalf Of
Kurt Wendt
Sent: Tuesday, March 14, 2017 3:23 PM
To: [email protected]
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]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
