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.
