WorkSheet And "Copy"

[Lazycop]

Hi,
i have a problem with Excel Application.

The Worksheet Method "Copy" not working correctly.

Here is sample of my code:
INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
INVOKE COPYSHEET "SELECT".
INVOKE COPYSHEET "COPY" USING COPYSHEET. ← working correctly
INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET. ← not working correctly

Have anyone test this Method?

Best Regards

Lazycop

[SkippyPB]

On Fri, 27 Jul 2012 13:45:25 -0500, Lazycop <gaoxi...@gmail.com>
wrote:

>Hi,
>i have a problem with Excel Application.
>
>The Worksheet Method "Copy" not working correctly.
>
>Here is sample of my code:
>INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
>INVOKE COPYSHEET "SELECT".

>INVOKE COPYSHEET "COPY" USING COPYSHEET. � working correctly
>INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET. � not working correctly

>
>Have anyone test this Method?
>
>Best Regards
>
>Lazycop
>

Are you so lazy to notice this is a COBOL newsgroup not an MS-EXCEL
newsgroup?

Regards,
--

////
(o o)
-oOO--(_)--OOo-

Do you think that when they asked George Washington for ID that he
just whipped out a quarter?
-- Steven Wright
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

[0rober...@gmail.com]

It looks to me that these are user defined objects and classes that are being invoked with the COBOL object oriented features using the COBOL INVOKE statement and you will need to ask the people who provided these classes and methods.

Robert

[Pete Dashwood]

Lazycop wrote:
> Hi,
> i have a problem with Excel Application.
>
> The Worksheet Method "Copy" not working correctly.

Yes it is. You said so yourself.

>
> Here is sample of my code:
> INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
> INVOKE COPYSHEET "SELECT".

> INVOKE COPYSHEET "COPY" USING COPYSHEET. �?� working correctly

There, it worked correctly...


> INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET. �?� not working
> correctly

Because you have not provided the correct method signature to the Excel
method.

Why are you trying to"OMIT" the first parameter? Did you intend to copy your
range to the clipboard? That is NOT the way to do it.

There are a few things you need to do if you want help:

1. Post here telling us the version of COBOL you are using.

2. Describe what KIND of object you are trying to COPY. (The Method
signature varies depnding on what you are copying.)

3. (And this is the MOST IMPORTANT) Look up the VBA description of the
Method and its signatures and make sure you have instantiated the required
objects before you invoke the method.

Meantime, try this:

INVOKE COPYSHEET "COPY"
USING NULL, COPYSHEET
END-INVOKE

However, as I am stabbing in the dark because you did not provide the needed
information, there's probably only a 50:50 chance it will work.

Pete.
--
"I used to write COBOL...now I can do anything."

[Pete Dashwood]

SkippyPB wrote:
> On Fri, 27 Jul 2012 13:45:25 -0500, Lazycop <gaoxi...@gmail.com>
> wrote:
>
>> Hi,
>> i have a problem with Excel Application.
>>
>> The Worksheet Method "Copy" not working correctly.
>>
>> Here is sample of my code:
>> INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
>> INVOKE COPYSHEET "SELECT".

>> INVOKE COPYSHEET "COPY" USING COPYSHEET. �?? working correctly
>> INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET. �?? not working

>> correctly
>>
>> Have anyone test this Method?
>>
>> Best Regards
>>
>> Lazycop
>>
>
> Are you so lazy to notice this is a COBOL newsgroup not an MS-EXCEL
> newsgroup?

Steve,

his example is COBOL and his problem is with a COBOL implementation using a
Microsoft Automation server.

An MS-EXCEL group would have little idea of what he is talking about.

Posting here is completely appropriate and doesn't warrant reprimand... :-)

[Pete Dashwood]

0rober...@gmail.com wrote:
> On Friday, July 27, 2012 7:45:25 PM UTC+1, Lazycop wrote:
>> Hi,
>>
>> i have a problem with Excel Application.
>>
>>
>>
>> The Worksheet Method "Copy" not working correctly.
>>
>>
>>
>> Here is sample of my code:
>>
>> INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
>>
>> INVOKE COPYSHEET "SELECT".
>>

>> INVOKE COPYSHEET "COPY" USING COPYSHEET. ? working correctly
>>
>> INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET. ? not working

>> correctly
>>
>>
>>
>> Have anyone test this Method?
>>
>>
>>
>> Best Regards
>>
>>
>>
>> Lazycop
>
> It looks to me that these are user defined objects and classes that
> are being invoked with the COBOL object oriented features using the
> COBOL INVOKE statement and you will need to ask the people who
> provided these classes and methods.
>
> Robert

They are not "user-defined" but other than that your response is completely
correct.

Your advice is also sound in that he SHOULD "ask the people who provided
these classes and methods." but unfortunately, said people don't speak COBOL
:-)

They DO provide full documentation of their classes and methods in VBA and
if you want to use them from COBOL, it is necessary to "translate them".

This is one of the drawbacks with COBOL; if you use it to manage standard
software like Excel, PowerPoint, Access DB, etc. all of the huge information
about these products is not in a readily assimilable form, for a COBOL
programmer. (At the very least you need to acquire a working knowledge of
Visual Basic...) And it is not just MS products; it is the same with any
Automation server (COM/OLE/ActiveX)

You CAN use COBOL just as you CAN make fire by rubbing sticks together.

But there are much better (and cheaper) ways to stay warm...

[Lazycop]

sorry!
my english is very poor!

my question is the method "copy" is not working in my micro focus cobol program.

Here is sample of my code:
INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
INVOKE COPYSHEET "SELECT".
INVOKE COPYSHEET "COPY" USING COPYSHEET.

INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET.

now, it is working correctly like this:

INVOKE COPYSHEET "COPY" USING COPYSHEET.

but not working like this:

INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET.

Best Regards

Lazycop

--http://compgroups.net/comp.lang.cobol/worksheet-and-copy/1498136

[Pete Dashwood]

Lazycop wrote:
> sorry!
> my english is very poor!
>
> my question is the method "copy" is not working in my micro focus
> cobol program.
>
> Here is sample of my code:
> INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
> INVOKE COPYSHEET "SELECT".

Don't select the whole sheet; specify a range.

> INVOKE COPYSHEET "COPY" USING COPYSHEET.
> INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET.

So, what error message did you receive?

Did you try pasting to see if the selection was copied to the clipboard?

Have you tried passing the address of a null reference instead of "OMITTED"?

Have you looked at the Micro Focus examples for Excel?

Do need to do a lot more homework...

[SkippyPB]

OK fine. My bad. Never seen a COBOL program use that construct but
have used something similar in Excel. Reprimand is rescinded. :)

Regards,
--

////
(o o)
-oOO--(_)--OOo-

"I plead contemporary insanity."
-- Unknown

[Rick Smith]

On Monday, July 30, 2012 9:42:44 AM UTC-4, Pete Dashwood wrote:
> Lazycop wrote:
>
> > sorry!
>
> > my english is very poor!
>
> >
>
> > my question is the method "copy" is not working in my micro focus
>
> > cobol program.
>
> >
>
> > Here is sample of my code:
>
> > INVOKE XLEXCEL "GETSHEETS" USING BY VALUE 1 RETURNING COPYSHEET.
>
> > INVOKE COPYSHEET "SELECT".
>
> Don't select the whole sheet; specify a range.

Huh!? The "Copy" method uses worksheets, not ranges.

> > INVOKE COPYSHEET "COPY" USING COPYSHEET.
>
> > INVOKE COPYSHEET "COPY" USING OMITTED COPYSHEET.

< http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.copy(v=vs.80).aspx >
=====
C#
public virtual void Copy (
[OptionalAttribute] Object Before,
[OptionalAttribute] Object After
)
Parameters

Before
The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After
The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.
=====

The following code example uses the Copy method to create copies of the current worksheet before Sheet1 and after Sheet3. The example uses the InnerObject property to access the underlying Microsoft.Office.Interop.Excel.Worksheet for Sheet1 and Sheet3.

C#
private void CopyWorksheet()
{
// Copy this worksheet before Sheet1.
this.Copy(Globals.Sheet1.InnerObject, missing);

// Copy this worksheet after Sheet3.
this.Copy(missing, Globals.Sheet3.InnerObject);
}
=====

The use of 'OMITTED' seems a reasonable COBOL substitute
for C# 'missing', but I couldn't locate a suitable reference
that defines 'missing'.

I appreciate that you are trying to help but you didn't
quite grasp what the OP was doing. For my part, I made an
effort but came up short.

[Pete Dashwood]

No Rick, I understand exactly what he is trying to do and I DID check what
he needs.

The COPY method can use many things including ranges.

(In fact, he is trying to COPY a range to the system clipboard.)

Here's a link to the MSDN definitions on this:

http://msdn.microsoft.com/en-us/library/office/aa223844(v=office.11).aspx

He isn't using C or C#; he is using COBOL...

I make no comment about your effort.

[Lazycop]

> No Rick, I understand exactly what he is trying to do and I DID check what
> he needs.

> The COPY method can use many things including ranges.

> (In fact, he is trying to COPY a range to the system clipboard.)

> Here's a link to the MSDN definitions on this:

> http://msdn.microsoft.com/en-us/library/office/aa223844(v=office.11).aspx

> He isn't using C or C#; he is using COBOL...

> I make no comment about your effort.

> Pete.
> --
> "I used to write COBOL...now I can do anything."

To Pete:
thank you very much.
but i'd like to say Rick Smith understand what i want.

>The COPY method can use many things including ranges.

yes,it is correct as you said.
but in a WorkSheet Object, the method "COPY" is provided for users to copy it self to another place.

And i know the method "COPY" which can copy range, because i used it in my another program.

however, i used to write vb and C#, And i know how to use the method in vb and C#.
But the problem is that I can not use it in COBOL.

there are some forms in WorkSheet "COPYSHEET", for example:
rows(1)'s Height is different to rows(2)' Height. or
cols(1)'s Width is different to cols(2)'s Width.
Now, if copy the range to new worksheet, the forms will be modified.
so i'd like to copy the whole worksheet after self.

i'm so sorry that peoples might not understand what i said.

Best Regards

Lazycop

[Rick Smith]

Perhaps these references will help.

< http://kb.microfocus.com/display/4/kb/article.aspx?aid=3932 >

< http://community.microfocus.com/microfocus/cobol/visual_cobol/w/wiki/527.visual-cobol-net-and-microsoft-office-combined-is-a-great-opportunity-for-a-little-crm.aspx >

[Lazycop]

to Steve
i'm sorry.
and i will provide more of informations about my problem if i post a new question.

to Robert
thanks for your reply.
i think it is provided by Microsoft Excel.

to Pete
this is my test program:
PROGRAM-ID. TESTEXCEL.
.
.
I-O-CONTROL.
CLASS-CONTROL.
MSEXCEL IS CLASS "$OLE$EXCEL.APPLICATION"
ENTRYCALLBACK IS CLASS "ENTRYCLL"
EXCEPTMGR IS CLASS "EXPTNMGR"
OLEEXCEPTMGR IS CLASS "OLEEXPT".
WORKING-STORAGE SECTION.
.
.
01 XLEXCEL OBJECT REFERENCE.
01 COPYWORKBOOKS OBJECT REFERENCE.
01 COPYWORKBOOK OBJECT REFERENCE.
01 COPYWORKSHEETS OBJECT REFERENCE.
01 COPYSHEET OBJECT REFERENCE.
LINKAGE SECTION.
01 LNKERRORNUMBER PIC X(4) COMP-5.
01 LNKERROROBJECT OBJECT REFERENCE.
01 LNKERRORTEXT OBJECT REFERENCE.
PROCEDURE DIVISION.
.
.
INVOKE MSEXCEL "NEW"
RETURNING XLEXCEL.
INVOKE XLEXCEL "SETVISIBLE"
USING BY VALUE 1.
INVOKE XLEXCEL "GETWORKBOOKS"
RETURNING COPYWORKBOOKS.
INVOKE COPYWORKBOOKS "OPEN"
USING BY CONTENT Z"D:\TESTEXCEL.XLS"
RETURNING COPYWORKBOOK.
INVOKE COPYWORKBOOK "GETWORKSHEETS"
RETURNING COPYWORKSHEETS.

INVOKE XLEXCEL "GETSHEETS"
USING BY VALUE 1
RETURNING COPYSHEET.
INVOKE COPYSHEET "SELECT".
INVOKE COPYSHEET "COPY"
USING COPYSHEET.

* INVOKE COPYSHEET "COPY"
* USING X"00"
* COPYSHEET.
* INVOKE COPYSHEET "COPY"
* USING NULL
* COPYSHEET.

INVOKE COPYSHEET "COPY"
USING OMITTED
COPYSHEET.

INVOKE COPYSHEET "FINALIZE"
RETURNING COPYSHEET.
INVOKE COPYWORKSHEETS "FINALIZE"
RETURNING COPYWORKSHEETS.
INVOKE COPYWORKBOOK "CLOSE"
USING BY VALUE 1
BY CONTENT Z"D:\TESTEXCEL.XLS"
INVOKE COPYWORKBOOK "FINALIZE"
RETURNING COPYWORKBOOK.
INVOKE COPYWORKBOOKS "FINALIZE"
RETURNING COPYWORKBOOKS.
INVOKE XLEXCEL "QUIT".
INVOKE XLEXCEL "FINALIZE"
RETURNING XLEXCEL.
.
.


And in VBA, it dosen't work like this:
Sheet1.Copy ,Sheet1
or
Sheet1.Copy Nothing,Sheet1

it works like this:
Sheet1.Copy NULL,Sheet1
or
Sheet1.Copy after:=Sheet1

if i want to copy a sheet to before the target sheet, now i can do like this:
Sheet1.Copy Sheet1
or
Sheet1.Copy before:=Sheet1

actually, i want to copy a sheet not a range to after of the target sheet.


Best Regards

Lazycop

[Lazycop]

to Rick
thank you.
> < http://kb.microfocus.com/display/4/kb/article.aspx?aid=3D3932 >
i can't get this page.
<Server Error in '/' Application.
Input string was not in a correct format.>

> < http://community.microfocus.com/microfocus/cobol/visual_cobol/w/wiki/527.=
> visual-cobol-net-and-microsoft-office-combined-is-a-great-opportunity-for-a=
> -little-crm.aspx >
i can't get this page.
<Page Not Found
Unfortunately, the page you've requested no longer exists. Please use the search form below to locate the information you're interested in.>

[Rick Smith]

On Tuesday, July 31, 2012 1:56:33 PM UTC-4, Lazycop wrote:
> to Rick
>
> thank you.
>
> > < http://kb.microfocus.com/display/4/kb/article.aspx?aid=3D3932 >
>
> i can't get this page.
>
> <Server Error in '/' Application.
>
> Input string was not in a correct format.>

=====
If I have a .Net class and I was to pass a null reference to it from Unmanaged COBOL. How can I do it ?

This is going via .Net COM Interop.

...

To get this worked as expected you need to pass a COM Variant object with the type set to DISP-E-PARAMNOTFOUND.

This denotes to COM that it is a missing parameters and the .Net Class is given a null object.

The COBOL code to do this is:-

invoke oleVariant "new" returning mynull
invoke mynull "getVariantAddress" returning ws-ptr
set address of lnk-var to ws-ptr
move DISP-E-PARAMNOTFOUND
to VARIANT-VT-SCODE of lnk-var
invoke ws-dnobj "TestMethod"
using by reference string1
by reference mynull
by value inta
by value intb
returning retval
=====

>
> > < http://community.microfocus.com/microfocus/cobol/visual_cobol/w/wiki/527.=
>
> > visual-cobol-net-and-microsoft-office-combined-is-a-great-opportunity-for-a=
>
> > -little-crm.aspx >
>
> i can't get this page.
>
> <Page Not Found
>
> Unfortunately, the page you've requested no longer exists. Please use the search form below to locate the information you're interested in.>

=====
program-id. Program1 as "VCMailMerge.Program1".

working-storage section.
*Word is not native .net, but encapsulated COM. In COM you have the opportunity to
*omitt optional parameters, this is not supported in most .net native languages. Hence,
*we have to provide "dummy" data for these. For this purpose we use the
* "System.Reflection.NullObject::Value
01 NullObject object value type System.Reflection.Missing::Value.

...

*> Create master document
invoke WrdApp::Documents::Add(NullObject, NullObject, wdBlank, NullObject) giving WrdDoc

...
=====

Both examples provide means for Micro Focus COBOL
programs to INVOKE methods on Microsoft supplied
software, where the method has optional parameters.
I don't know which of these will work for your
purposes.

[Rick Smith]

On Friday, July 27, 2012 2:45:25 PM UTC-4, Lazycop wrote:

> i have a problem with Excel Application.

[snip]

< http://community.microfocus.com/microfocus/cobol/net_express__server_express/f/19/t/8236.aspx >

[Lazycop]

to Rick
thank you very much!

> invoke oleVariant "new" returning mynull
> invoke mynull "getVariantAddress" returning ws-ptr
> set address of lnk-var to ws-ptr
> move DISP-E-PARAMNOTFOUND
> to VARIANT-VT-SCODE of lnk-var
> invoke ws-dnobj "TestMethod"
> using by reference string1
> by reference mynull
> by value inta
> by value intb
> returning retval

i got it.

invoke xlExcel "getMailSession" returning xlNull.
invoke copySheet "copy" using xlNull copySheet.

now it is working correctly!

i can get a Variant Object with method "getMailSession" or "getStatusBar".

thanks again!