Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

OLE with Excel

16 posts in Objects Last posting was on 2009-10-02 16:41:14.0Z
Chris Pollach Posted on 2009-09-22 19:03:27.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
Subject: OLE with Excel
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ab91f7f$1@forums-1-dub>
Date: 22 Sep 2009 12:03:27 -0700
X-Trace: forums-1-dub 1253646207 10.22.241.152 (22 Sep 2009 12:03:27 -0700)
X-Original-Trace: 22 Sep 2009 12:03:27 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9901
Article PK: 737031

All;

I have an OLE DataWindow session started with Excel. For user logistical
purposes, they need the File=>Open/Save/SaveAs menus disabled. I used to be
able to do this in external applications by getting the handle to the menu
objects and disabling them via PB script. However, in Excel 2003 and
higher - it seems that the menu objects were replaced with user objects
(grrr). So now, I seem to have no control on the menu items (that I know
of).

Soooo.... the "big question" is whether or not I can enable/disable the
Excel menus if I get a secondary OLE session active over to Excel and then
issue an ______ OLE command?

Any help would be appreciated!

--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com


Ivaylo Ivanov Posted on 2009-09-23 11:16:08.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 85
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aba0378$1@forums-1-dub>
Date: 23 Sep 2009 04:16:08 -0700
X-Trace: forums-1-dub 1253704568 10.22.241.152 (23 Sep 2009 04:16:08 -0700)
X-Original-Trace: 23 Sep 2009 04:16:08 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9902
Article PK: 737030

Hi, Chris

I don't know if this would be of help to you, but... the following macro for
older Excel versions shows sample item manipulation not using handles:

Sub a()
' Just for debug
' For Each cbar In CommandBars.ActiveMenuBar
' MsgBox (cbar.Name & " : " & cbar.NameLocal)
' Next

' Find and disable 3 operations in the menu bar
For Each ctl In CommandBars("Worksheet Menu Bar").Controls
If ctl.ID = 30002 Then 'File menu
MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
For Each ctl2 In ctl.Controls
MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
' Open, Save or Save As
If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
MsgBox ("Will be disabled")
ctl2.Enabled = False
End If
'If ctl2.Caption = "&Save" Then
'ctl2.Caption = "&Save for Ivo"
'End If
Next ctl2
End If
Next ctl

' Find and disable 3 operations in the Standard toolbar
For Each ctl2 In CommandBars("Standard").Controls
MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
' Open, Save or Save As
If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
MsgBox ("Will be disabled")
ctl2.Enabled = False
End If
Next ctl2

End Sub

Maybe it'll give you a clue how to use VBA's CommandBar functionality
(everything - menus and toolbars in Excel are referred as CommandBars). This
macro disables Open, Save and SaveAs items separatedly in the menu bar and
in the "Standard" toolbar. The items themselves are referred by their IDs
(Open = 23, Save = 3, SaveAs = 748). However, I've no idea if the IDs or the
CommandBar object is the same for Excel 2007, either how to convert this
macro to a working PB OLE script sequence. Notice that the items you want
disabled may be configured by the user to be shown in some other toolbars or
submenus (via Tools --> Customize ...... drag&drop). In this case the macro
would be cleared to universally traverse all the commandbars along with
their controls and find the desired items with these specific IDs.

Hope this helps a little

Kind regards,
Ivaylo

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ab91f7f$1@forums-1-dub...
> All;
>
> I have an OLE DataWindow session started with Excel. For user
> logistical purposes, they need the File=>Open/Save/SaveAs menus disabled.
> I used to be able to do this in external applications by getting the
> handle to the menu objects and disabling them via PB script. However, in
> Excel 2003 and higher - it seems that the menu objects were replaced with
> user objects (grrr). So now, I seem to have no control on the menu items
> (that I know of).
>
> Soooo.... the "big question" is whether or not I can enable/disable the
> Excel menus if I get a secondary OLE session active over to Excel and then
> issue an ______ OLE command?
>
> Any help would be appreciated!
>
> --
> Regards ... Chris
> ISUG - NA RUG Director
> http://chrispollach.pbdjmagazine.com
>
>
>


Ivaylo Ivanov Posted on 2009-09-23 11:47:31.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 106
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aba0ad3@forums-1-dub>
Date: 23 Sep 2009 04:47:31 -0700
X-Trace: forums-1-dub 1253706451 10.22.241.152 (23 Sep 2009 04:47:31 -0700)
X-Original-Trace: 23 Sep 2009 04:47:31 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9903
Article PK: 737032

And some links to explore:
http://msdn.microsoft.com/en-us/library/bb148786.aspx
http://msdn.microsoft.com/en-us/library/aa432098.aspx
http://www.ozgrid.com/forum/showthread.php?t=70258

Within Excel'2007 MSDN stated:
"The use of CommandBars in some Microsoft Office applications has been
superseded by the new Ribbon user interface. For more information, search
help for the keyword "Ribbon."..."
http://msdn.microsoft.com/en-us/library/bb608623.aspx
http://msdn.microsoft.com/en-us/library/bb386097.aspx
http://msdn.microsoft.com/en-us/library/bb772088.aspx
http://msdn.microsoft.com/en-us/library/bb386089.aspx

Anyway, in some pages it's stated that the old CommandBar system still works

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4aba0378$1@forums-1-dub...
> Hi, Chris
>
> I don't know if this would be of help to you, but... the following macro
> for older Excel versions shows sample item manipulation not using handles:
>
> Sub a()
> ' Just for debug
> ' For Each cbar In CommandBars.ActiveMenuBar
> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
> ' Next
>
> ' Find and disable 3 operations in the menu bar
> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
> If ctl.ID = 30002 Then 'File menu
> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
> For Each ctl2 In ctl.Controls
> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
> ' Open, Save or Save As
> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
> MsgBox ("Will be disabled")
> ctl2.Enabled = False
> End If
> 'If ctl2.Caption = "&Save" Then
> 'ctl2.Caption = "&Save for Ivo"
> 'End If
> Next ctl2
> End If
> Next ctl
>
> ' Find and disable 3 operations in the Standard toolbar
> For Each ctl2 In CommandBars("Standard").Controls
> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
> ' Open, Save or Save As
> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
> MsgBox ("Will be disabled")
> ctl2.Enabled = False
> End If
> Next ctl2
>
> End Sub
>
> Maybe it'll give you a clue how to use VBA's CommandBar functionality
> (everything - menus and toolbars in Excel are referred as CommandBars).
> This macro disables Open, Save and SaveAs items separatedly in the menu
> bar and in the "Standard" toolbar. The items themselves are referred by
> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
> the IDs or the CommandBar object is the same for Excel 2007, either how to
> convert this macro to a working PB OLE script sequence. Notice that the
> items you want disabled may be configured by the user to be shown in some
> other toolbars or submenus (via Tools --> Customize ...... drag&drop). In
> this case the macro would be cleared to universally traverse all the
> commandbars along with their controls and find the desired items with
> these specific IDs.
>
> Hope this helps a little
>
> Kind regards,
> Ivaylo
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ab91f7f$1@forums-1-dub...
>> All;
>>
>> I have an OLE DataWindow session started with Excel. For user
>> logistical purposes, they need the File=>Open/Save/SaveAs menus disabled.
>> I used to be able to do this in external applications by getting the
>> handle to the menu objects and disabling them via PB script. However, in
>> Excel 2003 and higher - it seems that the menu objects were replaced with
>> user objects (grrr). So now, I seem to have no control on the menu items
>> (that I know of).
>>
>> Soooo.... the "big question" is whether or not I can enable/disable
>> the Excel menus if I get a secondary OLE session active over to Excel and
>> then issue an ______ OLE command?
>>
>> Any help would be appreciated!
>>
>> --
>> Regards ... Chris
>> ISUG - NA RUG Director
>> http://chrispollach.pbdjmagazine.com
>>
>>
>>
>
>


Chris Pollach Posted on 2009-09-28 13:00:29.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 190
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac0b36d@forums-1-dub>
Date: 28 Sep 2009 06:00:29 -0700
X-Trace: forums-1-dub 1254142829 10.22.241.152 (28 Sep 2009 06:00:29 -0700)
X-Original-Trace: 28 Sep 2009 06:00:29 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9905
Article PK: 737037

Thanks Ivan;

That plus a little more reading and I have it working. :-)

For those following in my footsteps ... you need to start an OLE session
with Excel along with the DW's OLEActivate session. This allows you to
control Excel. You will also need to get a handle to the various objects
inside Excel - as follows:

-------------------------------------------------------------------------------------------

n_oo lo_ole // OLE Container
OLEObject lo_ole_wb // WorkBook Pointer
OLEObject lo_ole_ws // WorkSheet Pointer
OLEObject lo_ole_cb // CommandBar Pointer
OLEObject lo_ole_mb //
OLEObject lo_ole_ct // Control Pointer

lo_ole = CREATE n_oo // Instantiate Main OLE component

TRY // Start of Critical Code

li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start a
new session
IF li_rc < 0 THEN // Session OK?
oleruntimeerror lo_ex // NO=>Alloc Structure
lo_ex = create oleruntimeerror // Instantiate it
lo_ex.number = li_rc // Load ReturnCode
lo_ex.objectname = THIS.Classname( ) // Get My name
Choose Case lo_ex.number // Decipher error ...
CASE -1
lo_ex.text = "Invalid Call: the argument is the Object property of a
control"
CASE -2
lo_ex.text = "Excel name not found on this computer"
CASE -3
lo_ex.text = "Excel session could not be created"
CASE -4
lo_ex.text = "Could not connect to Excel"
CASE -9
lo_ex.text = "Other error"
CASE -15
lo_ex.text = "EXCEL is not loaded on this computer"
CASE -16
lo_ex.text = "Invalid argument"
CASE Else
lo_ex.text = "A connection to Excel could not be made"
End Choose
THROW lo_ex // Send an Error
RETURN li_rc // Return to caller
END IF

// Start the real DW Blob Worksheet
li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) // Activate an
OLE session

lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer to
main ComandBar
lo_ole_wb = lo_ole.Application.ActiveWorkBook
li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to Controls
in CB
li_count = lo_ole_ct.Controls.Count // Get # controls

FOR li_loop = 1 to li_count // Loop thru Menu controls
ls_title = lo_ole_ct.Controls(li_loop).Caption // Get "Text"
of Control
IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
Pos (Lower (ls_title) , "open", 1) > 0 OR &
Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
Sensitive menu?
lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable it!
END IF
NEXT

li_rc = +1 // Set RC to "OK"!

Catch ( RunTimeError lo_rte ) // Error?
li_rc = -1 // YES=>Set RC to "bad"
MessageBox ("OLE Start Session Error!", lo_rte.text)

Finally // End of TRY
lo_ole.Disconnectobject ( )

End TRY

Destroy (lo_ole) // Clean up OLE object

Return li_rc // Return to caller

-------------------------------------------------------------------

HTH

--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4aba0378$1@forums-1-dub...
> Hi, Chris
>
> I don't know if this would be of help to you, but... the following macro
> for older Excel versions shows sample item manipulation not using handles:
>
> Sub a()
> ' Just for debug
> ' For Each cbar In CommandBars.ActiveMenuBar
> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
> ' Next
>
> ' Find and disable 3 operations in the menu bar
> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
> If ctl.ID = 30002 Then 'File menu
> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
> For Each ctl2 In ctl.Controls
> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
> ' Open, Save or Save As
> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
> MsgBox ("Will be disabled")
> ctl2.Enabled = False
> End If
> 'If ctl2.Caption = "&Save" Then
> 'ctl2.Caption = "&Save for Ivo"
> 'End If
> Next ctl2
> End If
> Next ctl
>
> ' Find and disable 3 operations in the Standard toolbar
> For Each ctl2 In CommandBars("Standard").Controls
> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
> ' Open, Save or Save As
> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
> MsgBox ("Will be disabled")
> ctl2.Enabled = False
> End If
> Next ctl2
>
> End Sub
>
> Maybe it'll give you a clue how to use VBA's CommandBar functionality
> (everything - menus and toolbars in Excel are referred as CommandBars).
> This macro disables Open, Save and SaveAs items separatedly in the menu
> bar and in the "Standard" toolbar. The items themselves are referred by
> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
> the IDs or the CommandBar object is the same for Excel 2007, either how to
> convert this macro to a working PB OLE script sequence. Notice that the
> items you want disabled may be configured by the user to be shown in some
> other toolbars or submenus (via Tools --> Customize ...... drag&drop). In
> this case the macro would be cleared to universally traverse all the
> commandbars along with their controls and find the desired items with
> these specific IDs.
>
> Hope this helps a little
>
> Kind regards,
> Ivaylo
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ab91f7f$1@forums-1-dub...
>> All;
>>
>> I have an OLE DataWindow session started with Excel. For user
>> logistical purposes, they need the File=>Open/Save/SaveAs menus disabled.
>> I used to be able to do this in external applications by getting the
>> handle to the menu objects and disabling them via PB script. However, in
>> Excel 2003 and higher - it seems that the menu objects were replaced with
>> user objects (grrr). So now, I seem to have no control on the menu items
>> (that I know of).
>>
>> Soooo.... the "big question" is whether or not I can enable/disable
>> the Excel menus if I get a secondary OLE session active over to Excel and
>> then issue an ______ OLE command?
>>
>> Any help would be appreciated!
>>
>> --
>> Regards ... Chris
>> ISUG - NA RUG Director
>> http://chrispollach.pbdjmagazine.com
>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-28 15:01:34.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 214
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac0cfce@forums-1-dub>
Date: 28 Sep 2009 08:01:34 -0700
X-Trace: forums-1-dub 1254150094 10.22.241.152 (28 Sep 2009 08:01:34 -0700)
X-Original-Trace: 28 Sep 2009 08:01:34 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9907
Article PK: 737033

Good news for Excel 2007 users that CommandBar mechanizm still works :-)

Chris, I've just wanted to turn your attention to the constants you use:
= CommandBars(1) - Can you be sure that index of 1 within the CommandBars is
always the main CommandBar?
= Caption property is not readonly - it CAN be customized, so these words
"open", "new" and "save" might not be properly located
= what about the customized toolbars (with the pictures)? This script is
supposed to manipulate Enabled state of the menu items, but the toolbar
items must be processed separatedly. And what if your Excel installation has
been customized by the user thus having more than one instance of these
"open", "new" and "save" commands in the menus or in the toolbars?

Just a thoughts to consider

Best regards,
Ivaylo (not Ivan ;-))

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ac0b36d@forums-1-dub...
> Thanks Ivan;
>
> That plus a little more reading and I have it working. :-)
>
> For those following in my footsteps ... you need to start an OLE session
> with Excel along with the DW's OLEActivate session. This allows you to
> control Excel. You will also need to get a handle to the various objects
> inside Excel - as follows:
>
> -------------------------------------------------------------------------------------------
>
> n_oo lo_ole // OLE Container
> OLEObject lo_ole_wb // WorkBook Pointer
> OLEObject lo_ole_ws // WorkSheet Pointer
> OLEObject lo_ole_cb // CommandBar Pointer
> OLEObject lo_ole_mb //
> OLEObject lo_ole_ct // Control Pointer
>
> lo_ole = CREATE n_oo // Instantiate Main OLE component
>
> TRY // Start of Critical Code
>
> li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start a
> new session
> IF li_rc < 0 THEN // Session OK?
> oleruntimeerror lo_ex // NO=>Alloc Structure
> lo_ex = create oleruntimeerror // Instantiate it
> lo_ex.number = li_rc // Load ReturnCode
> lo_ex.objectname = THIS.Classname( ) // Get My name
> Choose Case lo_ex.number // Decipher error ...
> CASE -1
> lo_ex.text = "Invalid Call: the argument is the Object property of a
> control"
> CASE -2
> lo_ex.text = "Excel name not found on this computer"
> CASE -3
> lo_ex.text = "Excel session could not be created"
> CASE -4
> lo_ex.text = "Could not connect to Excel"
> CASE -9
> lo_ex.text = "Other error"
> CASE -15
> lo_ex.text = "EXCEL is not loaded on this computer"
> CASE -16
> lo_ex.text = "Invalid argument"
> CASE Else
> lo_ex.text = "A connection to Excel could not be made"
> End Choose
> THROW lo_ex // Send an Error
> RETURN li_rc // Return to caller
> END IF
>
> // Start the real DW Blob Worksheet
> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) // Activate
> an OLE session
>
> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer to
> main ComandBar
> lo_ole_wb = lo_ole.Application.ActiveWorkBook
> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
> Controls in CB
> li_count = lo_ole_ct.Controls.Count // Get # controls
>
> FOR li_loop = 1 to li_count // Loop thru Menu controls
> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get "Text"
> of Control
> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
> Pos (Lower (ls_title) , "open", 1) > 0 OR &
> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
> Sensitive menu?
> lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable
> it!
> END IF
> NEXT
>
> li_rc = +1 // Set RC to "OK"!
>
> Catch ( RunTimeError lo_rte ) // Error?
> li_rc = -1 // YES=>Set RC to "bad"
> MessageBox ("OLE Start Session Error!", lo_rte.text)
>
> Finally // End of TRY
> lo_ole.Disconnectobject ( )
>
> End TRY
>
> Destroy (lo_ole) // Clean up OLE object
>
> Return li_rc // Return to caller
>
> -------------------------------------------------------------------
>
> HTH
>
> --
> Regards ... Chris
> ISUG - NA RUG Director
> http://chrispollach.pbdjmagazine.com
>
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4aba0378$1@forums-1-dub...
>> Hi, Chris
>>
>> I don't know if this would be of help to you, but... the following macro
>> for older Excel versions shows sample item manipulation not using
>> handles:
>>
>> Sub a()
>> ' Just for debug
>> ' For Each cbar In CommandBars.ActiveMenuBar
>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>> ' Next
>>
>> ' Find and disable 3 operations in the menu bar
>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>> If ctl.ID = 30002 Then 'File menu
>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>> For Each ctl2 In ctl.Controls
>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>> ' Open, Save or Save As
>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>> MsgBox ("Will be disabled")
>> ctl2.Enabled = False
>> End If
>> 'If ctl2.Caption = "&Save" Then
>> 'ctl2.Caption = "&Save for Ivo"
>> 'End If
>> Next ctl2
>> End If
>> Next ctl
>>
>> ' Find and disable 3 operations in the Standard toolbar
>> For Each ctl2 In CommandBars("Standard").Controls
>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>> ' Open, Save or Save As
>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>> MsgBox ("Will be disabled")
>> ctl2.Enabled = False
>> End If
>> Next ctl2
>>
>> End Sub
>>
>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>> (everything - menus and toolbars in Excel are referred as CommandBars).
>> This macro disables Open, Save and SaveAs items separatedly in the menu
>> bar and in the "Standard" toolbar. The items themselves are referred by
>> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
>> the IDs or the CommandBar object is the same for Excel 2007, either how
>> to convert this macro to a working PB OLE script sequence. Notice that
>> the items you want disabled may be configured by the user to be shown in
>> some other toolbars or submenus (via Tools --> Customize ......
>> drag&drop). In this case the macro would be cleared to universally
>> traverse all the commandbars along with their controls and find the
>> desired items with these specific IDs.
>>
>> Hope this helps a little
>>
>> Kind regards,
>> Ivaylo
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4ab91f7f$1@forums-1-dub...
>>> All;
>>>
>>> I have an OLE DataWindow session started with Excel. For user
>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>> disabled. I used to be able to do this in external applications by
>>> getting the handle to the menu objects and disabling them via PB script.
>>> However, in Excel 2003 and higher - it seems that the menu objects were
>>> replaced with user objects (grrr). So now, I seem to have no control on
>>> the menu items (that I know of).
>>>
>>> Soooo.... the "big question" is whether or not I can enable/disable
>>> the Excel menus if I get a secondary OLE session active over to Excel
>>> and then issue an ______ OLE command?
>>>
>>> Any help would be appreciated!
>>>
>>> --
>>> Regards ... Chris
>>> ISUG - NA RUG Director
>>> http://chrispollach.pbdjmagazine.com
>>>
>>>
>>>
>>
>>
>
>


Chris Pollach Posted on 2009-09-29 12:10:55.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 226
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac1f94f$1@forums-1-dub>
Date: 29 Sep 2009 05:10:55 -0700
X-Trace: forums-1-dub 1254226255 10.22.241.152 (29 Sep 2009 05:10:55 -0700)
X-Original-Trace: 29 Sep 2009 05:10:55 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9909
Article PK: 737038

Good points. My coding was just a prototype when I posted it. I have since
refined it to loop through the various CommandBars and attach the problem
generically. I did no think about the aspect of the user customizing Excel -
in fact, I did not know that you could change the menu names. Thanks for
that information. hopefully, my users will not do that (currently 800+ users
on the system I am implementing this on).

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac0cfce@forums-1-dub...
> Good news for Excel 2007 users that CommandBar mechanizm still works :-)
>
> Chris, I've just wanted to turn your attention to the constants you use:
> = CommandBars(1) - Can you be sure that index of 1 within the CommandBars
> is always the main CommandBar?
> = Caption property is not readonly - it CAN be customized, so these words
> "open", "new" and "save" might not be properly located
> = what about the customized toolbars (with the pictures)? This script is
> supposed to manipulate Enabled state of the menu items, but the toolbar
> items must be processed separatedly. And what if your Excel installation
> has been customized by the user thus having more than one instance of
> these "open", "new" and "save" commands in the menus or in the toolbars?
>
> Just a thoughts to consider
>
> Best regards,
> Ivaylo (not Ivan ;-))
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ac0b36d@forums-1-dub...
>> Thanks Ivan;
>>
>> That plus a little more reading and I have it working. :-)
>>
>> For those following in my footsteps ... you need to start an OLE session
>> with Excel along with the DW's OLEActivate session. This allows you to
>> control Excel. You will also need to get a handle to the various objects
>> inside Excel - as follows:
>>
>> -------------------------------------------------------------------------------------------
>>
>> n_oo lo_ole // OLE Container
>> OLEObject lo_ole_wb // WorkBook Pointer
>> OLEObject lo_ole_ws // WorkSheet Pointer
>> OLEObject lo_ole_cb // CommandBar Pointer
>> OLEObject lo_ole_mb //
>> OLEObject lo_ole_ct // Control Pointer
>>
>> lo_ole = CREATE n_oo // Instantiate Main OLE component
>>
>> TRY // Start of Critical Code
>>
>> li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start a
>> new session
>> IF li_rc < 0 THEN // Session OK?
>> oleruntimeerror lo_ex // NO=>Alloc Structure
>> lo_ex = create oleruntimeerror // Instantiate it
>> lo_ex.number = li_rc // Load ReturnCode
>> lo_ex.objectname = THIS.Classname( ) // Get My name
>> Choose Case lo_ex.number // Decipher error ...
>> CASE -1
>> lo_ex.text = "Invalid Call: the argument is the Object property of a
>> control"
>> CASE -2
>> lo_ex.text = "Excel name not found on this computer"
>> CASE -3
>> lo_ex.text = "Excel session could not be created"
>> CASE -4
>> lo_ex.text = "Could not connect to Excel"
>> CASE -9
>> lo_ex.text = "Other error"
>> CASE -15
>> lo_ex.text = "EXCEL is not loaded on this computer"
>> CASE -16
>> lo_ex.text = "Invalid argument"
>> CASE Else
>> lo_ex.text = "A connection to Excel could not be made"
>> End Choose
>> THROW lo_ex // Send an Error
>> RETURN li_rc // Return to caller
>> END IF
>>
>> // Start the real DW Blob Worksheet
>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) // Activate
>> an OLE session
>>
>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer to
>> main ComandBar
>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>> Controls in CB
>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>
>> FOR li_loop = 1 to li_count // Loop thru Menu controls
>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get "Text"
>> of Control
>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
>> Sensitive menu?
>> lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable
>> it!
>> END IF
>> NEXT
>>
>> li_rc = +1 // Set RC to "OK"!
>>
>> Catch ( RunTimeError lo_rte ) // Error?
>> li_rc = -1 // YES=>Set RC to "bad"
>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>
>> Finally // End of TRY
>> lo_ole.Disconnectobject ( )
>>
>> End TRY
>>
>> Destroy (lo_ole) // Clean up OLE object
>>
>> Return li_rc // Return to caller
>>
>> -------------------------------------------------------------------
>>
>> HTH
>>
>> --
>> Regards ... Chris
>> ISUG - NA RUG Director
>> http://chrispollach.pbdjmagazine.com
>>
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4aba0378$1@forums-1-dub...
>>> Hi, Chris
>>>
>>> I don't know if this would be of help to you, but... the following macro
>>> for older Excel versions shows sample item manipulation not using
>>> handles:
>>>
>>> Sub a()
>>> ' Just for debug
>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>> ' Next
>>>
>>> ' Find and disable 3 operations in the menu bar
>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>> If ctl.ID = 30002 Then 'File menu
>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>> For Each ctl2 In ctl.Controls
>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>> ' Open, Save or Save As
>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>> MsgBox ("Will be disabled")
>>> ctl2.Enabled = False
>>> End If
>>> 'If ctl2.Caption = "&Save" Then
>>> 'ctl2.Caption = "&Save for Ivo"
>>> 'End If
>>> Next ctl2
>>> End If
>>> Next ctl
>>>
>>> ' Find and disable 3 operations in the Standard toolbar
>>> For Each ctl2 In CommandBars("Standard").Controls
>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>> ' Open, Save or Save As
>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>> MsgBox ("Will be disabled")
>>> ctl2.Enabled = False
>>> End If
>>> Next ctl2
>>>
>>> End Sub
>>>
>>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>>> (everything - menus and toolbars in Excel are referred as CommandBars).
>>> This macro disables Open, Save and SaveAs items separatedly in the menu
>>> bar and in the "Standard" toolbar. The items themselves are referred by
>>> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
>>> the IDs or the CommandBar object is the same for Excel 2007, either how
>>> to convert this macro to a working PB OLE script sequence. Notice that
>>> the items you want disabled may be configured by the user to be shown in
>>> some other toolbars or submenus (via Tools --> Customize ......
>>> drag&drop). In this case the macro would be cleared to universally
>>> traverse all the commandbars along with their controls and find the
>>> desired items with these specific IDs.
>>>
>>> Hope this helps a little
>>>
>>> Kind regards,
>>> Ivaylo
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4ab91f7f$1@forums-1-dub...
>>>> All;
>>>>
>>>> I have an OLE DataWindow session started with Excel. For user
>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>> disabled. I used to be able to do this in external applications by
>>>> getting the handle to the menu objects and disabling them via PB
>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>> objects were replaced with user objects (grrr). So now, I seem to have
>>>> no control on the menu items (that I know of).
>>>>
>>>> Soooo.... the "big question" is whether or not I can enable/disable
>>>> the Excel menus if I get a secondary OLE session active over to Excel
>>>> and then issue an ______ OLE command?
>>>>
>>>> Any help would be appreciated!
>>>>
>>>> --
>>>> Regards ... Chris
>>>> ISUG - NA RUG Director
>>>> http://chrispollach.pbdjmagazine.com
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-29 14:58:08.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 238
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac22080@forums-1-dub>
Date: 29 Sep 2009 07:58:08 -0700
X-Trace: forums-1-dub 1254236288 10.22.241.152 (29 Sep 2009 07:58:08 -0700)
X-Original-Trace: 29 Sep 2009 07:58:08 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9911
Article PK: 737040

Have you explored the ID property of the looped controls? Are they the same
between different Excel versions?
I mean "open" command always having ID = 23, "save" command always having ID
= 3 etc.
If it's so, you might wish to rely on the ID, not on the Caption and this
way would natively trap the user-customized captions.

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ac1f94f$1@forums-1-dub...
> Good points. My coding was just a prototype when I posted it. I have since
> refined it to loop through the various CommandBars and attach the problem
> generically. I did no think about the aspect of the user customizing
> Excel - in fact, I did not know that you could change the menu names.
> Thanks for that information. hopefully, my users will not do that
> (currently 800+ users on the system I am implementing this on).
>
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4ac0cfce@forums-1-dub...
>> Good news for Excel 2007 users that CommandBar mechanizm still works :-)
>>
>> Chris, I've just wanted to turn your attention to the constants you use:
>> = CommandBars(1) - Can you be sure that index of 1 within the CommandBars
>> is always the main CommandBar?
>> = Caption property is not readonly - it CAN be customized, so these words
>> "open", "new" and "save" might not be properly located
>> = what about the customized toolbars (with the pictures)? This script is
>> supposed to manipulate Enabled state of the menu items, but the toolbar
>> items must be processed separatedly. And what if your Excel installation
>> has been customized by the user thus having more than one instance of
>> these "open", "new" and "save" commands in the menus or in the toolbars?
>>
>> Just a thoughts to consider
>>
>> Best regards,
>> Ivaylo (not Ivan ;-))
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4ac0b36d@forums-1-dub...
>>> Thanks Ivan;
>>>
>>> That plus a little more reading and I have it working. :-)
>>>
>>> For those following in my footsteps ... you need to start an OLE session
>>> with Excel along with the DW's OLEActivate session. This allows you to
>>> control Excel. You will also need to get a handle to the various objects
>>> inside Excel - as follows:
>>>
>>> -------------------------------------------------------------------------------------------
>>>
>>> n_oo lo_ole // OLE Container
>>> OLEObject lo_ole_wb // WorkBook Pointer
>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>> OLEObject lo_ole_cb // CommandBar Pointer
>>> OLEObject lo_ole_mb //
>>> OLEObject lo_ole_ct // Control Pointer
>>>
>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>> component
>>>
>>> TRY // Start of Critical Code
>>>
>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start
>>> a new session
>>> IF li_rc < 0 THEN // Session OK?
>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>> lo_ex = create oleruntimeerror // Instantiate it
>>> lo_ex.number = li_rc // Load ReturnCode
>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>> Choose Case lo_ex.number // Decipher error ...
>>> CASE -1
>>> lo_ex.text = "Invalid Call: the argument is the Object property of a
>>> control"
>>> CASE -2
>>> lo_ex.text = "Excel name not found on this computer"
>>> CASE -3
>>> lo_ex.text = "Excel session could not be created"
>>> CASE -4
>>> lo_ex.text = "Could not connect to Excel"
>>> CASE -9
>>> lo_ex.text = "Other error"
>>> CASE -15
>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>> CASE -16
>>> lo_ex.text = "Invalid argument"
>>> CASE Else
>>> lo_ex.text = "A connection to Excel could not be made"
>>> End Choose
>>> THROW lo_ex // Send an Error
>>> RETURN li_rc // Return to caller
>>> END IF
>>>
>>> // Start the real DW Blob Worksheet
>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) // Activate
>>> an OLE session
>>>
>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer to
>>> main ComandBar
>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>> Controls in CB
>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>
>>> FOR li_loop = 1 to li_count // Loop thru Menu controls
>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>> "Text" of Control
>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
>>> Sensitive menu?
>>> lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable
>>> it!
>>> END IF
>>> NEXT
>>>
>>> li_rc = +1 // Set RC to "OK"!
>>>
>>> Catch ( RunTimeError lo_rte ) // Error?
>>> li_rc = -1 // YES=>Set RC to "bad"
>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>
>>> Finally // End of TRY
>>> lo_ole.Disconnectobject ( )
>>>
>>> End TRY
>>>
>>> Destroy (lo_ole) // Clean up OLE object
>>>
>>> Return li_rc // Return to caller
>>>
>>> -------------------------------------------------------------------
>>>
>>> HTH
>>>
>>> --
>>> Regards ... Chris
>>> ISUG - NA RUG Director
>>> http://chrispollach.pbdjmagazine.com
>>>
>>>
>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>> news:4aba0378$1@forums-1-dub...
>>>> Hi, Chris
>>>>
>>>> I don't know if this would be of help to you, but... the following
>>>> macro for older Excel versions shows sample item manipulation not using
>>>> handles:
>>>>
>>>> Sub a()
>>>> ' Just for debug
>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>> ' Next
>>>>
>>>> ' Find and disable 3 operations in the menu bar
>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>> If ctl.ID = 30002 Then 'File menu
>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>> For Each ctl2 In ctl.Controls
>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>> ' Open, Save or Save As
>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>> MsgBox ("Will be disabled")
>>>> ctl2.Enabled = False
>>>> End If
>>>> 'If ctl2.Caption = "&Save" Then
>>>> 'ctl2.Caption = "&Save for Ivo"
>>>> 'End If
>>>> Next ctl2
>>>> End If
>>>> Next ctl
>>>>
>>>> ' Find and disable 3 operations in the Standard toolbar
>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>> ' Open, Save or Save As
>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>> MsgBox ("Will be disabled")
>>>> ctl2.Enabled = False
>>>> End If
>>>> Next ctl2
>>>>
>>>> End Sub
>>>>
>>>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>>>> (everything - menus and toolbars in Excel are referred as CommandBars).
>>>> This macro disables Open, Save and SaveAs items separatedly in the menu
>>>> bar and in the "Standard" toolbar. The items themselves are referred by
>>>> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
>>>> the IDs or the CommandBar object is the same for Excel 2007, either how
>>>> to convert this macro to a working PB OLE script sequence. Notice that
>>>> the items you want disabled may be configured by the user to be shown
>>>> in some other toolbars or submenus (via Tools --> Customize ......
>>>> drag&drop). In this case the macro would be cleared to universally
>>>> traverse all the commandbars along with their controls and find the
>>>> desired items with these specific IDs.
>>>>
>>>> Hope this helps a little
>>>>
>>>> Kind regards,
>>>> Ivaylo
>>>>
>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>> news:4ab91f7f$1@forums-1-dub...
>>>>> All;
>>>>>
>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>> disabled. I used to be able to do this in external applications by
>>>>> getting the handle to the menu objects and disabling them via PB
>>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>>> objects were replaced with user objects (grrr). So now, I seem to have
>>>>> no control on the menu items (that I know of).
>>>>>
>>>>> Soooo.... the "big question" is whether or not I can enable/disable
>>>>> the Excel menus if I get a secondary OLE session active over to Excel
>>>>> and then issue an ______ OLE command?
>>>>>
>>>>> Any help would be appreciated!
>>>>>
>>>>> --
>>>>> Regards ... Chris
>>>>> ISUG - NA RUG Director
>>>>> http://chrispollach.pbdjmagazine.com
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-29 15:03:33.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 248
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac221c5@forums-1-dub>
Date: 29 Sep 2009 08:03:33 -0700
X-Trace: forums-1-dub 1254236613 10.22.241.152 (29 Sep 2009 08:03:33 -0700)
X-Original-Trace: 29 Sep 2009 08:03:33 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9912
Article PK: 737041

Ah, forget to mention the possibility for the end-user to install Excel with
non-English menus:
"Neu", "Offnen", "Speichern" for German MS Office

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac22080@forums-1-dub...
> Have you explored the ID property of the looped controls? Are they the
> same between different Excel versions?
> I mean "open" command always having ID = 23, "save" command always having
> ID = 3 etc.
> If it's so, you might wish to rely on the ID, not on the Caption and this
> way would natively trap the user-customized captions.
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ac1f94f$1@forums-1-dub...
>> Good points. My coding was just a prototype when I posted it. I have
>> since refined it to loop through the various CommandBars and attach the
>> problem generically. I did no think about the aspect of the user
>> customizing Excel - in fact, I did not know that you could change the
>> menu names. Thanks for that information. hopefully, my users will not do
>> that (currently 800+ users on the system I am implementing this on).
>>
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4ac0cfce@forums-1-dub...
>>> Good news for Excel 2007 users that CommandBar mechanizm still works :-)
>>>
>>> Chris, I've just wanted to turn your attention to the constants you use:
>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>> CommandBars is always the main CommandBar?
>>> = Caption property is not readonly - it CAN be customized, so these
>>> words "open", "new" and "save" might not be properly located
>>> = what about the customized toolbars (with the pictures)? This script is
>>> supposed to manipulate Enabled state of the menu items, but the toolbar
>>> items must be processed separatedly. And what if your Excel installation
>>> has been customized by the user thus having more than one instance of
>>> these "open", "new" and "save" commands in the menus or in the toolbars?
>>>
>>> Just a thoughts to consider
>>>
>>> Best regards,
>>> Ivaylo (not Ivan ;-))
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4ac0b36d@forums-1-dub...
>>>> Thanks Ivan;
>>>>
>>>> That plus a little more reading and I have it working. :-)
>>>>
>>>> For those following in my footsteps ... you need to start an OLE
>>>> session with Excel along with the DW's OLEActivate session. This allows
>>>> you to control Excel. You will also need to get a handle to the various
>>>> objects inside Excel - as follows:
>>>>
>>>> -------------------------------------------------------------------------------------------
>>>>
>>>> n_oo lo_ole // OLE Container
>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>> OLEObject lo_ole_mb //
>>>> OLEObject lo_ole_ct // Control Pointer
>>>>
>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>> component
>>>>
>>>> TRY // Start of Critical Code
>>>>
>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start
>>>> a new session
>>>> IF li_rc < 0 THEN // Session OK?
>>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>> lo_ex.number = li_rc // Load ReturnCode
>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>> Choose Case lo_ex.number // Decipher error ...
>>>> CASE -1
>>>> lo_ex.text = "Invalid Call: the argument is the Object property of a
>>>> control"
>>>> CASE -2
>>>> lo_ex.text = "Excel name not found on this computer"
>>>> CASE -3
>>>> lo_ex.text = "Excel session could not be created"
>>>> CASE -4
>>>> lo_ex.text = "Could not connect to Excel"
>>>> CASE -9
>>>> lo_ex.text = "Other error"
>>>> CASE -15
>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>> CASE -16
>>>> lo_ex.text = "Invalid argument"
>>>> CASE Else
>>>> lo_ex.text = "A connection to Excel could not be made"
>>>> End Choose
>>>> THROW lo_ex // Send an Error
>>>> RETURN li_rc // Return to caller
>>>> END IF
>>>>
>>>> // Start the real DW Blob Worksheet
>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>> Activate an OLE session
>>>>
>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer
>>>> to main ComandBar
>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>> Controls in CB
>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>
>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>> controls
>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>> "Text" of Control
>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
>>>> Sensitive menu?
>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable
>>>> it!
>>>> END IF
>>>> NEXT
>>>>
>>>> li_rc = +1 // Set RC to "OK"!
>>>>
>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>
>>>> Finally // End of TRY
>>>> lo_ole.Disconnectobject ( )
>>>>
>>>> End TRY
>>>>
>>>> Destroy (lo_ole) // Clean up OLE object
>>>>
>>>> Return li_rc // Return to caller
>>>>
>>>> -------------------------------------------------------------------
>>>>
>>>> HTH
>>>>
>>>> --
>>>> Regards ... Chris
>>>> ISUG - NA RUG Director
>>>> http://chrispollach.pbdjmagazine.com
>>>>
>>>>
>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>> news:4aba0378$1@forums-1-dub...
>>>>> Hi, Chris
>>>>>
>>>>> I don't know if this would be of help to you, but... the following
>>>>> macro for older Excel versions shows sample item manipulation not
>>>>> using handles:
>>>>>
>>>>> Sub a()
>>>>> ' Just for debug
>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>> ' Next
>>>>>
>>>>> ' Find and disable 3 operations in the menu bar
>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>> If ctl.ID = 30002 Then 'File menu
>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>> For Each ctl2 In ctl.Controls
>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>> ' Open, Save or Save As
>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>> MsgBox ("Will be disabled")
>>>>> ctl2.Enabled = False
>>>>> End If
>>>>> 'If ctl2.Caption = "&Save" Then
>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>> 'End If
>>>>> Next ctl2
>>>>> End If
>>>>> Next ctl
>>>>>
>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>> ' Open, Save or Save As
>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>> MsgBox ("Will be disabled")
>>>>> ctl2.Enabled = False
>>>>> End If
>>>>> Next ctl2
>>>>>
>>>>> End Sub
>>>>>
>>>>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>>>>> (everything - menus and toolbars in Excel are referred as
>>>>> CommandBars). This macro disables Open, Save and SaveAs items
>>>>> separatedly in the menu bar and in the "Standard" toolbar. The items
>>>>> themselves are referred by their IDs (Open = 23, Save = 3, SaveAs =
>>>>> 748). However, I've no idea if the IDs or the CommandBar object is the
>>>>> same for Excel 2007, either how to convert this macro to a working PB
>>>>> OLE script sequence. Notice that the items you want disabled may be
>>>>> configured by the user to be shown in some other toolbars or submenus
>>>>> (via Tools --> Customize ...... drag&drop). In this case the macro
>>>>> would be cleared to universally traverse all the commandbars along
>>>>> with their controls and find the desired items with these specific
>>>>> IDs.
>>>>>
>>>>> Hope this helps a little
>>>>>
>>>>> Kind regards,
>>>>> Ivaylo
>>>>>
>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>> All;
>>>>>>
>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>> disabled. I used to be able to do this in external applications by
>>>>>> getting the handle to the menu objects and disabling them via PB
>>>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>>>> objects were replaced with user objects (grrr). So now, I seem to
>>>>>> have no control on the menu items (that I know of).
>>>>>>
>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>
>>>>>> Any help would be appreciated!
>>>>>>
>>>>>> --
>>>>>> Regards ... Chris
>>>>>> ISUG - NA RUG Director
>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Chris Pollach Posted on 2009-09-29 16:11:55.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 260
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac231cb$1@forums-1-dub>
Date: 29 Sep 2009 09:11:55 -0700
X-Trace: forums-1-dub 1254240715 10.22.241.152 (29 Sep 2009 09:11:55 -0700)
X-Original-Trace: 29 Sep 2009 09:11:55 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9913
Article PK: 737039

Yes ... being in Canada - I have the problem with English and French
versions of Excel! :-)

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac221c5@forums-1-dub...
> Ah, forget to mention the possibility for the end-user to install Excel
> with non-English menus:
> "Neu", "Offnen", "Speichern" for German MS Office
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4ac22080@forums-1-dub...
>> Have you explored the ID property of the looped controls? Are they the
>> same between different Excel versions?
>> I mean "open" command always having ID = 23, "save" command always having
>> ID = 3 etc.
>> If it's so, you might wish to rely on the ID, not on the Caption and this
>> way would natively trap the user-customized captions.
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4ac1f94f$1@forums-1-dub...
>>> Good points. My coding was just a prototype when I posted it. I have
>>> since refined it to loop through the various CommandBars and attach the
>>> problem generically. I did no think about the aspect of the user
>>> customizing Excel - in fact, I did not know that you could change the
>>> menu names. Thanks for that information. hopefully, my users will not do
>>> that (currently 800+ users on the system I am implementing this on).
>>>
>>>
>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>> news:4ac0cfce@forums-1-dub...
>>>> Good news for Excel 2007 users that CommandBar mechanizm still works
>>>> :-)
>>>>
>>>> Chris, I've just wanted to turn your attention to the constants you
>>>> use:
>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>> CommandBars is always the main CommandBar?
>>>> = Caption property is not readonly - it CAN be customized, so these
>>>> words "open", "new" and "save" might not be properly located
>>>> = what about the customized toolbars (with the pictures)? This script
>>>> is supposed to manipulate Enabled state of the menu items, but the
>>>> toolbar items must be processed separatedly. And what if your Excel
>>>> installation has been customized by the user thus having more than one
>>>> instance of these "open", "new" and "save" commands in the menus or in
>>>> the toolbars?
>>>>
>>>> Just a thoughts to consider
>>>>
>>>> Best regards,
>>>> Ivaylo (not Ivan ;-))
>>>>
>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>> news:4ac0b36d@forums-1-dub...
>>>>> Thanks Ivan;
>>>>>
>>>>> That plus a little more reading and I have it working. :-)
>>>>>
>>>>> For those following in my footsteps ... you need to start an OLE
>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>> allows you to control Excel. You will also need to get a handle to the
>>>>> various objects inside Excel - as follows:
>>>>>
>>>>> -------------------------------------------------------------------------------------------
>>>>>
>>>>> n_oo lo_ole // OLE Container
>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>> OLEObject lo_ole_mb //
>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>
>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>> component
>>>>>
>>>>> TRY // Start of Critical Code
>>>>>
>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>> Start a new session
>>>>> IF li_rc < 0 THEN // Session OK?
>>>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>> CASE -1
>>>>> lo_ex.text = "Invalid Call: the argument is the Object property of
>>>>> a control"
>>>>> CASE -2
>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>> CASE -3
>>>>> lo_ex.text = "Excel session could not be created"
>>>>> CASE -4
>>>>> lo_ex.text = "Could not connect to Excel"
>>>>> CASE -9
>>>>> lo_ex.text = "Other error"
>>>>> CASE -15
>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>> CASE -16
>>>>> lo_ex.text = "Invalid argument"
>>>>> CASE Else
>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>> End Choose
>>>>> THROW lo_ex // Send an Error
>>>>> RETURN li_rc // Return to caller
>>>>> END IF
>>>>>
>>>>> // Start the real DW Blob Worksheet
>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>> Activate an OLE session
>>>>>
>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer
>>>>> to main ComandBar
>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>> Controls in CB
>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>
>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>> controls
>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>> "Text" of Control
>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
>>>>> Sensitive menu?
>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>> YES=>Disable it!
>>>>> END IF
>>>>> NEXT
>>>>>
>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>
>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>
>>>>> Finally // End of TRY
>>>>> lo_ole.Disconnectobject ( )
>>>>>
>>>>> End TRY
>>>>>
>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>
>>>>> Return li_rc // Return to caller
>>>>>
>>>>> -------------------------------------------------------------------
>>>>>
>>>>> HTH
>>>>>
>>>>> --
>>>>> Regards ... Chris
>>>>> ISUG - NA RUG Director
>>>>> http://chrispollach.pbdjmagazine.com
>>>>>
>>>>>
>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>> news:4aba0378$1@forums-1-dub...
>>>>>> Hi, Chris
>>>>>>
>>>>>> I don't know if this would be of help to you, but... the following
>>>>>> macro for older Excel versions shows sample item manipulation not
>>>>>> using handles:
>>>>>>
>>>>>> Sub a()
>>>>>> ' Just for debug
>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>> ' Next
>>>>>>
>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>> For Each ctl2 In ctl.Controls
>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>> ' Open, Save or Save As
>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>> MsgBox ("Will be disabled")
>>>>>> ctl2.Enabled = False
>>>>>> End If
>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>> 'End If
>>>>>> Next ctl2
>>>>>> End If
>>>>>> Next ctl
>>>>>>
>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>> ' Open, Save or Save As
>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>> MsgBox ("Will be disabled")
>>>>>> ctl2.Enabled = False
>>>>>> End If
>>>>>> Next ctl2
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>>>>>> (everything - menus and toolbars in Excel are referred as
>>>>>> CommandBars). This macro disables Open, Save and SaveAs items
>>>>>> separatedly in the menu bar and in the "Standard" toolbar. The items
>>>>>> themselves are referred by their IDs (Open = 23, Save = 3, SaveAs =
>>>>>> 748). However, I've no idea if the IDs or the CommandBar object is
>>>>>> the same for Excel 2007, either how to convert this macro to a
>>>>>> working PB OLE script sequence. Notice that the items you want
>>>>>> disabled may be configured by the user to be shown in some other
>>>>>> toolbars or submenus (via Tools --> Customize ...... drag&drop). In
>>>>>> this case the macro would be cleared to universally traverse all the
>>>>>> commandbars along with their controls and find the desired items with
>>>>>> these specific IDs.
>>>>>>
>>>>>> Hope this helps a little
>>>>>>
>>>>>> Kind regards,
>>>>>> Ivaylo
>>>>>>
>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>> All;
>>>>>>>
>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>> disabled. I used to be able to do this in external applications by
>>>>>>> getting the handle to the menu objects and disabling them via PB
>>>>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>>>>> objects were replaced with user objects (grrr). So now, I seem to
>>>>>>> have no control on the menu items (that I know of).
>>>>>>>
>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>
>>>>>>> Any help would be appreciated!
>>>>>>>
>>>>>>> --
>>>>>>> Regards ... Chris
>>>>>>> ISUG - NA RUG Director
>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-29 17:15:55.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub> <4ac231cb$1@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 272
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac240cb@forums-1-dub>
Date: 29 Sep 2009 10:15:55 -0700
X-Trace: forums-1-dub 1254244555 10.22.241.152 (29 Sep 2009 10:15:55 -0700)
X-Original-Trace: 29 Sep 2009 10:15:55 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9914
Article PK: 737043

Laughed very often on some translations of the English menus ;-) ... being
in Bulgaria

So, stepping on the ID property if it's constantly corresponding to the same
operation in different Excel versions would be a better option IMHO

Good luck!

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ac231cb$1@forums-1-dub...
>
> Yes ... being in Canada - I have the problem with English and French
> versions of Excel! :-)
>
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4ac221c5@forums-1-dub...
>> Ah, forget to mention the possibility for the end-user to install Excel
>> with non-English menus:
>> "Neu", "Offnen", "Speichern" for German MS Office
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4ac22080@forums-1-dub...
>>> Have you explored the ID property of the looped controls? Are they the
>>> same between different Excel versions?
>>> I mean "open" command always having ID = 23, "save" command always
>>> having ID = 3 etc.
>>> If it's so, you might wish to rely on the ID, not on the Caption and
>>> this way would natively trap the user-customized captions.
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4ac1f94f$1@forums-1-dub...
>>>> Good points. My coding was just a prototype when I posted it. I have
>>>> since refined it to loop through the various CommandBars and attach the
>>>> problem generically. I did no think about the aspect of the user
>>>> customizing Excel - in fact, I did not know that you could change the
>>>> menu names. Thanks for that information. hopefully, my users will not
>>>> do that (currently 800+ users on the system I am implementing this on).
>>>>
>>>>
>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>> news:4ac0cfce@forums-1-dub...
>>>>> Good news for Excel 2007 users that CommandBar mechanizm still works
>>>>> :-)
>>>>>
>>>>> Chris, I've just wanted to turn your attention to the constants you
>>>>> use:
>>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>>> CommandBars is always the main CommandBar?
>>>>> = Caption property is not readonly - it CAN be customized, so these
>>>>> words "open", "new" and "save" might not be properly located
>>>>> = what about the customized toolbars (with the pictures)? This script
>>>>> is supposed to manipulate Enabled state of the menu items, but the
>>>>> toolbar items must be processed separatedly. And what if your Excel
>>>>> installation has been customized by the user thus having more than one
>>>>> instance of these "open", "new" and "save" commands in the menus or in
>>>>> the toolbars?
>>>>>
>>>>> Just a thoughts to consider
>>>>>
>>>>> Best regards,
>>>>> Ivaylo (not Ivan ;-))
>>>>>
>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>> news:4ac0b36d@forums-1-dub...
>>>>>> Thanks Ivan;
>>>>>>
>>>>>> That plus a little more reading and I have it working. :-)
>>>>>>
>>>>>> For those following in my footsteps ... you need to start an OLE
>>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>>> allows you to control Excel. You will also need to get a handle to
>>>>>> the various objects inside Excel - as follows:
>>>>>>
>>>>>> -------------------------------------------------------------------------------------------
>>>>>>
>>>>>> n_oo lo_ole // OLE Container
>>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>>> OLEObject lo_ole_mb //
>>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>>
>>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>>> component
>>>>>>
>>>>>> TRY // Start of Critical Code
>>>>>>
>>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>>> Start a new session
>>>>>> IF li_rc < 0 THEN // Session OK?
>>>>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>>> CASE -1
>>>>>> lo_ex.text = "Invalid Call: the argument is the Object property of
>>>>>> a control"
>>>>>> CASE -2
>>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>>> CASE -3
>>>>>> lo_ex.text = "Excel session could not be created"
>>>>>> CASE -4
>>>>>> lo_ex.text = "Could not connect to Excel"
>>>>>> CASE -9
>>>>>> lo_ex.text = "Other error"
>>>>>> CASE -15
>>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>>> CASE -16
>>>>>> lo_ex.text = "Invalid argument"
>>>>>> CASE Else
>>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>>> End Choose
>>>>>> THROW lo_ex // Send an Error
>>>>>> RETURN li_rc // Return to caller
>>>>>> END IF
>>>>>>
>>>>>> // Start the real DW Blob Worksheet
>>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>>> Activate an OLE session
>>>>>>
>>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer
>>>>>> to main ComandBar
>>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>>> Controls in CB
>>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>>
>>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>>> controls
>>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>>> "Text" of Control
>>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
>>>>>> Sensitive menu?
>>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>>> YES=>Disable it!
>>>>>> END IF
>>>>>> NEXT
>>>>>>
>>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>>
>>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>>
>>>>>> Finally // End of TRY
>>>>>> lo_ole.Disconnectobject ( )
>>>>>>
>>>>>> End TRY
>>>>>>
>>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>>
>>>>>> Return li_rc // Return to caller
>>>>>>
>>>>>> -------------------------------------------------------------------
>>>>>>
>>>>>> HTH
>>>>>>
>>>>>> --
>>>>>> Regards ... Chris
>>>>>> ISUG - NA RUG Director
>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>
>>>>>>
>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>> news:4aba0378$1@forums-1-dub...
>>>>>>> Hi, Chris
>>>>>>>
>>>>>>> I don't know if this would be of help to you, but... the following
>>>>>>> macro for older Excel versions shows sample item manipulation not
>>>>>>> using handles:
>>>>>>>
>>>>>>> Sub a()
>>>>>>> ' Just for debug
>>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>>> ' Next
>>>>>>>
>>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>>> For Each ctl2 In ctl.Controls
>>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>> ' Open, Save or Save As
>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>> MsgBox ("Will be disabled")
>>>>>>> ctl2.Enabled = False
>>>>>>> End If
>>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>>> 'End If
>>>>>>> Next ctl2
>>>>>>> End If
>>>>>>> Next ctl
>>>>>>>
>>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>> ' Open, Save or Save As
>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>> MsgBox ("Will be disabled")
>>>>>>> ctl2.Enabled = False
>>>>>>> End If
>>>>>>> Next ctl2
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar
>>>>>>> functionality (everything - menus and toolbars in Excel are referred
>>>>>>> as CommandBars). This macro disables Open, Save and SaveAs items
>>>>>>> separatedly in the menu bar and in the "Standard" toolbar. The items
>>>>>>> themselves are referred by their IDs (Open = 23, Save = 3, SaveAs =
>>>>>>> 748). However, I've no idea if the IDs or the CommandBar object is
>>>>>>> the same for Excel 2007, either how to convert this macro to a
>>>>>>> working PB OLE script sequence. Notice that the items you want
>>>>>>> disabled may be configured by the user to be shown in some other
>>>>>>> toolbars or submenus (via Tools --> Customize ...... drag&drop). In
>>>>>>> this case the macro would be cleared to universally traverse all the
>>>>>>> commandbars along with their controls and find the desired items
>>>>>>> with these specific IDs.
>>>>>>>
>>>>>>> Hope this helps a little
>>>>>>>
>>>>>>> Kind regards,
>>>>>>> Ivaylo
>>>>>>>
>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>>> All;
>>>>>>>>
>>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>>> disabled. I used to be able to do this in external applications by
>>>>>>>> getting the handle to the menu objects and disabling them via PB
>>>>>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>>>>>> objects were replaced with user objects (grrr). So now, I seem to
>>>>>>>> have no control on the menu items (that I know of).
>>>>>>>>
>>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>>
>>>>>>>> Any help would be appreciated!
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards ... Chris
>>>>>>>> ISUG - NA RUG Director
>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Chris Pollach Posted on 2009-09-29 18:29:02.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub> <4ac231cb$1@forums-1-dub> <4ac240cb@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 284
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac251ee@forums-1-dub>
Date: 29 Sep 2009 11:29:02 -0700
X-Trace: forums-1-dub 1254248942 10.22.241.152 (29 Sep 2009 11:29:02 -0700)
X-Original-Trace: 29 Sep 2009 11:29:02 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9915
Article PK: 737045

Yes .. I have changed my code now to go after ID's.
It now works seamlessly on the English and French Excel versions. :-)

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac240cb@forums-1-dub...
> Laughed very often on some translations of the English menus ;-) ... being
> in Bulgaria
>
> So, stepping on the ID property if it's constantly corresponding to the
> same operation in different Excel versions would be a better option IMHO
>
> Good luck!
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ac231cb$1@forums-1-dub...
>>
>> Yes ... being in Canada - I have the problem with English and French
>> versions of Excel! :-)
>>
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4ac221c5@forums-1-dub...
>>> Ah, forget to mention the possibility for the end-user to install Excel
>>> with non-English menus:
>>> "Neu", "Offnen", "Speichern" for German MS Office
>>>
>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>> news:4ac22080@forums-1-dub...
>>>> Have you explored the ID property of the looped controls? Are they the
>>>> same between different Excel versions?
>>>> I mean "open" command always having ID = 23, "save" command always
>>>> having ID = 3 etc.
>>>> If it's so, you might wish to rely on the ID, not on the Caption and
>>>> this way would natively trap the user-customized captions.
>>>>
>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>> news:4ac1f94f$1@forums-1-dub...
>>>>> Good points. My coding was just a prototype when I posted it. I have
>>>>> since refined it to loop through the various CommandBars and attach
>>>>> the problem generically. I did no think about the aspect of the user
>>>>> customizing Excel - in fact, I did not know that you could change the
>>>>> menu names. Thanks for that information. hopefully, my users will not
>>>>> do that (currently 800+ users on the system I am implementing this
>>>>> on).
>>>>>
>>>>>
>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>> news:4ac0cfce@forums-1-dub...
>>>>>> Good news for Excel 2007 users that CommandBar mechanizm still works
>>>>>> :-)
>>>>>>
>>>>>> Chris, I've just wanted to turn your attention to the constants you
>>>>>> use:
>>>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>>>> CommandBars is always the main CommandBar?
>>>>>> = Caption property is not readonly - it CAN be customized, so these
>>>>>> words "open", "new" and "save" might not be properly located
>>>>>> = what about the customized toolbars (with the pictures)? This script
>>>>>> is supposed to manipulate Enabled state of the menu items, but the
>>>>>> toolbar items must be processed separatedly. And what if your Excel
>>>>>> installation has been customized by the user thus having more than
>>>>>> one instance of these "open", "new" and "save" commands in the menus
>>>>>> or in the toolbars?
>>>>>>
>>>>>> Just a thoughts to consider
>>>>>>
>>>>>> Best regards,
>>>>>> Ivaylo (not Ivan ;-))
>>>>>>
>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>> news:4ac0b36d@forums-1-dub...
>>>>>>> Thanks Ivan;
>>>>>>>
>>>>>>> That plus a little more reading and I have it working. :-)
>>>>>>>
>>>>>>> For those following in my footsteps ... you need to start an OLE
>>>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>>>> allows you to control Excel. You will also need to get a handle to
>>>>>>> the various objects inside Excel - as follows:
>>>>>>>
>>>>>>> -------------------------------------------------------------------------------------------
>>>>>>>
>>>>>>> n_oo lo_ole // OLE Container
>>>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>>>> OLEObject lo_ole_mb //
>>>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>>>
>>>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>>>> component
>>>>>>>
>>>>>>> TRY // Start of Critical Code
>>>>>>>
>>>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>>>> Start a new session
>>>>>>> IF li_rc < 0 THEN // Session OK?
>>>>>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>>>> CASE -1
>>>>>>> lo_ex.text = "Invalid Call: the argument is the Object property
>>>>>>> of a control"
>>>>>>> CASE -2
>>>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>>>> CASE -3
>>>>>>> lo_ex.text = "Excel session could not be created"
>>>>>>> CASE -4
>>>>>>> lo_ex.text = "Could not connect to Excel"
>>>>>>> CASE -9
>>>>>>> lo_ex.text = "Other error"
>>>>>>> CASE -15
>>>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>>>> CASE -16
>>>>>>> lo_ex.text = "Invalid argument"
>>>>>>> CASE Else
>>>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>>>> End Choose
>>>>>>> THROW lo_ex // Send an Error
>>>>>>> RETURN li_rc // Return to caller
>>>>>>> END IF
>>>>>>>
>>>>>>> // Start the real DW Blob Worksheet
>>>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>>>> Activate an OLE session
>>>>>>>
>>>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get
>>>>>>> Pointer to main ComandBar
>>>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>>>> Controls in CB
>>>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>>>
>>>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>>>> controls
>>>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>>>> "Text" of Control
>>>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this
>>>>>>> a Sensitive menu?
>>>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>>>> YES=>Disable it!
>>>>>>> END IF
>>>>>>> NEXT
>>>>>>>
>>>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>>>
>>>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>>>
>>>>>>> Finally // End of TRY
>>>>>>> lo_ole.Disconnectobject ( )
>>>>>>>
>>>>>>> End TRY
>>>>>>>
>>>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>>>
>>>>>>> Return li_rc // Return to caller
>>>>>>>
>>>>>>> -------------------------------------------------------------------
>>>>>>>
>>>>>>> HTH
>>>>>>>
>>>>>>> --
>>>>>>> Regards ... Chris
>>>>>>> ISUG - NA RUG Director
>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>
>>>>>>>
>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>> news:4aba0378$1@forums-1-dub...
>>>>>>>> Hi, Chris
>>>>>>>>
>>>>>>>> I don't know if this would be of help to you, but... the following
>>>>>>>> macro for older Excel versions shows sample item manipulation not
>>>>>>>> using handles:
>>>>>>>>
>>>>>>>> Sub a()
>>>>>>>> ' Just for debug
>>>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>>>> ' Next
>>>>>>>>
>>>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>>>> For Each ctl2 In ctl.Controls
>>>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " &
>>>>>>>> ctl2.Caption)
>>>>>>>> ' Open, Save or Save As
>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>> ctl2.Enabled = False
>>>>>>>> End If
>>>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>>>> 'End If
>>>>>>>> Next ctl2
>>>>>>>> End If
>>>>>>>> Next ctl
>>>>>>>>
>>>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>>> ' Open, Save or Save As
>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>> ctl2.Enabled = False
>>>>>>>> End If
>>>>>>>> Next ctl2
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar
>>>>>>>> functionality (everything - menus and toolbars in Excel are
>>>>>>>> referred as CommandBars). This macro disables Open, Save and SaveAs
>>>>>>>> items separatedly in the menu bar and in the "Standard" toolbar.
>>>>>>>> The items themselves are referred by their IDs (Open = 23, Save =
>>>>>>>> 3, SaveAs = 748). However, I've no idea if the IDs or the
>>>>>>>> CommandBar object is the same for Excel 2007, either how to convert
>>>>>>>> this macro to a working PB OLE script sequence. Notice that the
>>>>>>>> items you want disabled may be configured by the user to be shown
>>>>>>>> in some other toolbars or submenus (via Tools --> Customize ......
>>>>>>>> drag&drop). In this case the macro would be cleared to universally
>>>>>>>> traverse all the commandbars along with their controls and find the
>>>>>>>> desired items with these specific IDs.
>>>>>>>>
>>>>>>>> Hope this helps a little
>>>>>>>>
>>>>>>>> Kind regards,
>>>>>>>> Ivaylo
>>>>>>>>
>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>>>> All;
>>>>>>>>>
>>>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>>>> disabled. I used to be able to do this in external applications by
>>>>>>>>> getting the handle to the menu objects and disabling them via PB
>>>>>>>>> script. However, in Excel 2003 and higher - it seems that the menu
>>>>>>>>> objects were replaced with user objects (grrr). So now, I seem to
>>>>>>>>> have no control on the menu items (that I know of).
>>>>>>>>>
>>>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>>>
>>>>>>>>> Any help would be appreciated!
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards ... Chris
>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-30 07:03:28.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub> <4ac231cb$1@forums-1-dub> <4ac240cb@forums-1-dub> <4ac251ee@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 295
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac302c0@forums-1-dub>
Date: 30 Sep 2009 00:03:28 -0700
X-Trace: forums-1-dub 1254294208 10.22.241.152 (30 Sep 2009 00:03:28 -0700)
X-Original-Trace: 30 Sep 2009 00:03:28 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9916
Article PK: 737047

That's pefect!

Have you compared IDs between different Excel versions to ensure this works
with older ones?

:-)

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ac251ee@forums-1-dub...
>
> Yes .. I have changed my code now to go after ID's.
> It now works seamlessly on the English and French Excel versions. :-)
>
>
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4ac240cb@forums-1-dub...
>> Laughed very often on some translations of the English menus ;-) ...
>> being in Bulgaria
>>
>> So, stepping on the ID property if it's constantly corresponding to the
>> same operation in different Excel versions would be a better option IMHO
>>
>> Good luck!
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4ac231cb$1@forums-1-dub...
>>>
>>> Yes ... being in Canada - I have the problem with English and French
>>> versions of Excel! :-)
>>>
>>>
>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>> news:4ac221c5@forums-1-dub...
>>>> Ah, forget to mention the possibility for the end-user to install Excel
>>>> with non-English menus:
>>>> "Neu", "Offnen", "Speichern" for German MS Office
>>>>
>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>> news:4ac22080@forums-1-dub...
>>>>> Have you explored the ID property of the looped controls? Are they the
>>>>> same between different Excel versions?
>>>>> I mean "open" command always having ID = 23, "save" command always
>>>>> having ID = 3 etc.
>>>>> If it's so, you might wish to rely on the ID, not on the Caption and
>>>>> this way would natively trap the user-customized captions.
>>>>>
>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>> news:4ac1f94f$1@forums-1-dub...
>>>>>> Good points. My coding was just a prototype when I posted it. I have
>>>>>> since refined it to loop through the various CommandBars and attach
>>>>>> the problem generically. I did no think about the aspect of the user
>>>>>> customizing Excel - in fact, I did not know that you could change the
>>>>>> menu names. Thanks for that information. hopefully, my users will not
>>>>>> do that (currently 800+ users on the system I am implementing this
>>>>>> on).
>>>>>>
>>>>>>
>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>> news:4ac0cfce@forums-1-dub...
>>>>>>> Good news for Excel 2007 users that CommandBar mechanizm still works
>>>>>>> :-)
>>>>>>>
>>>>>>> Chris, I've just wanted to turn your attention to the constants you
>>>>>>> use:
>>>>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>>>>> CommandBars is always the main CommandBar?
>>>>>>> = Caption property is not readonly - it CAN be customized, so these
>>>>>>> words "open", "new" and "save" might not be properly located
>>>>>>> = what about the customized toolbars (with the pictures)? This
>>>>>>> script is supposed to manipulate Enabled state of the menu items,
>>>>>>> but the toolbar items must be processed separatedly. And what if
>>>>>>> your Excel installation has been customized by the user thus having
>>>>>>> more than one instance of these "open", "new" and "save" commands in
>>>>>>> the menus or in the toolbars?
>>>>>>>
>>>>>>> Just a thoughts to consider
>>>>>>>
>>>>>>> Best regards,
>>>>>>> Ivaylo (not Ivan ;-))
>>>>>>>
>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>> news:4ac0b36d@forums-1-dub...
>>>>>>>> Thanks Ivan;
>>>>>>>>
>>>>>>>> That plus a little more reading and I have it working. :-)
>>>>>>>>
>>>>>>>> For those following in my footsteps ... you need to start an OLE
>>>>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>>>>> allows you to control Excel. You will also need to get a handle to
>>>>>>>> the various objects inside Excel - as follows:
>>>>>>>>
>>>>>>>> -------------------------------------------------------------------------------------------
>>>>>>>>
>>>>>>>> n_oo lo_ole // OLE Container
>>>>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>>>>> OLEObject lo_ole_mb //
>>>>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>>>>
>>>>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>>>>> component
>>>>>>>>
>>>>>>>> TRY // Start of Critical Code
>>>>>>>>
>>>>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>>>>> Start a new session
>>>>>>>> IF li_rc < 0 THEN // Session OK?
>>>>>>>> oleruntimeerror lo_ex // NO=>Alloc Structure
>>>>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>>>>> CASE -1
>>>>>>>> lo_ex.text = "Invalid Call: the argument is the Object property
>>>>>>>> of a control"
>>>>>>>> CASE -2
>>>>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>>>>> CASE -3
>>>>>>>> lo_ex.text = "Excel session could not be created"
>>>>>>>> CASE -4
>>>>>>>> lo_ex.text = "Could not connect to Excel"
>>>>>>>> CASE -9
>>>>>>>> lo_ex.text = "Other error"
>>>>>>>> CASE -15
>>>>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>>>>> CASE -16
>>>>>>>> lo_ex.text = "Invalid argument"
>>>>>>>> CASE Else
>>>>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>>>>> End Choose
>>>>>>>> THROW lo_ex // Send an Error
>>>>>>>> RETURN li_rc // Return to caller
>>>>>>>> END IF
>>>>>>>>
>>>>>>>> // Start the real DW Blob Worksheet
>>>>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>>>>> Activate an OLE session
>>>>>>>>
>>>>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get
>>>>>>>> Pointer to main ComandBar
>>>>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
>>>>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>>>>> Controls in CB
>>>>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>>>>
>>>>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>>>>> controls
>>>>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>>>>> "Text" of Control
>>>>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this
>>>>>>>> a Sensitive menu?
>>>>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>>>>> YES=>Disable it!
>>>>>>>> END IF
>>>>>>>> NEXT
>>>>>>>>
>>>>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>>>>
>>>>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>>>>
>>>>>>>> Finally // End of TRY
>>>>>>>> lo_ole.Disconnectobject ( )
>>>>>>>>
>>>>>>>> End TRY
>>>>>>>>
>>>>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>>>>
>>>>>>>> Return li_rc // Return to caller
>>>>>>>>
>>>>>>>> -------------------------------------------------------------------
>>>>>>>>
>>>>>>>> HTH
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards ... Chris
>>>>>>>> ISUG - NA RUG Director
>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>
>>>>>>>>
>>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>>> news:4aba0378$1@forums-1-dub...
>>>>>>>>> Hi, Chris
>>>>>>>>>
>>>>>>>>> I don't know if this would be of help to you, but... the following
>>>>>>>>> macro for older Excel versions shows sample item manipulation not
>>>>>>>>> using handles:
>>>>>>>>>
>>>>>>>>> Sub a()
>>>>>>>>> ' Just for debug
>>>>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>>>>> ' Next
>>>>>>>>>
>>>>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>>>>> For Each ctl2 In ctl.Controls
>>>>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " &
>>>>>>>>> ctl2.Caption)
>>>>>>>>> ' Open, Save or Save As
>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>> ctl2.Enabled = False
>>>>>>>>> End If
>>>>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>>>>> 'End If
>>>>>>>>> Next ctl2
>>>>>>>>> End If
>>>>>>>>> Next ctl
>>>>>>>>>
>>>>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>>>> ' Open, Save or Save As
>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>> ctl2.Enabled = False
>>>>>>>>> End If
>>>>>>>>> Next ctl2
>>>>>>>>>
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar
>>>>>>>>> functionality (everything - menus and toolbars in Excel are
>>>>>>>>> referred as CommandBars). This macro disables Open, Save and
>>>>>>>>> SaveAs items separatedly in the menu bar and in the "Standard"
>>>>>>>>> toolbar. The items themselves are referred by their IDs (Open =
>>>>>>>>> 23, Save = 3, SaveAs = 748). However, I've no idea if the IDs or
>>>>>>>>> the CommandBar object is the same for Excel 2007, either how to
>>>>>>>>> convert this macro to a working PB OLE script sequence. Notice
>>>>>>>>> that the items you want disabled may be configured by the user to
>>>>>>>>> be shown in some other toolbars or submenus (via Tools -->
>>>>>>>>> Customize ...... drag&drop). In this case the macro would be
>>>>>>>>> cleared to universally traverse all the commandbars along with
>>>>>>>>> their controls and find the desired items with these specific IDs.
>>>>>>>>>
>>>>>>>>> Hope this helps a little
>>>>>>>>>
>>>>>>>>> Kind regards,
>>>>>>>>> Ivaylo
>>>>>>>>>
>>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>>>>> All;
>>>>>>>>>>
>>>>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>>>>> disabled. I used to be able to do this in external applications
>>>>>>>>>> by getting the handle to the menu objects and disabling them via
>>>>>>>>>> PB script. However, in Excel 2003 and higher - it seems that the
>>>>>>>>>> menu objects were replaced with user objects (grrr). So now, I
>>>>>>>>>> seem to have no control on the menu items (that I know of).
>>>>>>>>>>
>>>>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>>>>
>>>>>>>>>> Any help would be appreciated!
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Regards ... Chris
>>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Ivaylo Ivanov Posted on 2009-09-30 07:06:05.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub> <4ac231cb$1@forums-1-dub> <4ac240cb@forums-1-dub> <4ac251ee@forums-1-dub> <4ac302c0@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 305
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac3035d@forums-1-dub>
Date: 30 Sep 2009 00:06:05 -0700
X-Trace: forums-1-dub 1254294365 10.22.241.152 (30 Sep 2009 00:06:05 -0700)
X-Original-Trace: 30 Sep 2009 00:06:05 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9917
Article PK: 737050

sorry for my "morning" grammar! I meant "PERFECT" :-)
Anybody, please more coffee ;-)

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac302c0@forums-1-dub...
> That's pefect!
>
> Have you compared IDs between different Excel versions to ensure this
> works with older ones?
>
> :-)
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ac251ee@forums-1-dub...
>>
>> Yes .. I have changed my code now to go after ID's.
>> It now works seamlessly on the English and French Excel versions. :-)
>>
>>
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4ac240cb@forums-1-dub...
>>> Laughed very often on some translations of the English menus ;-) ...
>>> being in Bulgaria
>>>
>>> So, stepping on the ID property if it's constantly corresponding to the
>>> same operation in different Excel versions would be a better option IMHO
>>>
>>> Good luck!
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4ac231cb$1@forums-1-dub...
>>>>
>>>> Yes ... being in Canada - I have the problem with English and French
>>>> versions of Excel! :-)
>>>>
>>>>
>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>> news:4ac221c5@forums-1-dub...
>>>>> Ah, forget to mention the possibility for the end-user to install
>>>>> Excel with non-English menus:
>>>>> "Neu", "Offnen", "Speichern" for German MS Office
>>>>>
>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>> news:4ac22080@forums-1-dub...
>>>>>> Have you explored the ID property of the looped controls? Are they
>>>>>> the same between different Excel versions?
>>>>>> I mean "open" command always having ID = 23, "save" command always
>>>>>> having ID = 3 etc.
>>>>>> If it's so, you might wish to rely on the ID, not on the Caption and
>>>>>> this way would natively trap the user-customized captions.
>>>>>>
>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>> news:4ac1f94f$1@forums-1-dub...
>>>>>>> Good points. My coding was just a prototype when I posted it. I have
>>>>>>> since refined it to loop through the various CommandBars and attach
>>>>>>> the problem generically. I did no think about the aspect of the user
>>>>>>> customizing Excel - in fact, I did not know that you could change
>>>>>>> the menu names. Thanks for that information. hopefully, my users
>>>>>>> will not do that (currently 800+ users on the system I am
>>>>>>> implementing this on).
>>>>>>>
>>>>>>>
>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>> news:4ac0cfce@forums-1-dub...
>>>>>>>> Good news for Excel 2007 users that CommandBar mechanizm still
>>>>>>>> works :-)
>>>>>>>>
>>>>>>>> Chris, I've just wanted to turn your attention to the constants you
>>>>>>>> use:
>>>>>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>>>>>> CommandBars is always the main CommandBar?
>>>>>>>> = Caption property is not readonly - it CAN be customized, so these
>>>>>>>> words "open", "new" and "save" might not be properly located
>>>>>>>> = what about the customized toolbars (with the pictures)? This
>>>>>>>> script is supposed to manipulate Enabled state of the menu items,
>>>>>>>> but the toolbar items must be processed separatedly. And what if
>>>>>>>> your Excel installation has been customized by the user thus having
>>>>>>>> more than one instance of these "open", "new" and "save" commands
>>>>>>>> in the menus or in the toolbars?
>>>>>>>>
>>>>>>>> Just a thoughts to consider
>>>>>>>>
>>>>>>>> Best regards,
>>>>>>>> Ivaylo (not Ivan ;-))
>>>>>>>>
>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>> news:4ac0b36d@forums-1-dub...
>>>>>>>>> Thanks Ivan;
>>>>>>>>>
>>>>>>>>> That plus a little more reading and I have it working. :-)
>>>>>>>>>
>>>>>>>>> For those following in my footsteps ... you need to start an OLE
>>>>>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>>>>>> allows you to control Excel. You will also need to get a handle to
>>>>>>>>> the various objects inside Excel - as follows:
>>>>>>>>>
>>>>>>>>> -------------------------------------------------------------------------------------------
>>>>>>>>>
>>>>>>>>> n_oo lo_ole // OLE Container
>>>>>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>>>>>> OLEObject lo_ole_mb //
>>>>>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>>>>>
>>>>>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>>>>>> component
>>>>>>>>>
>>>>>>>>> TRY // Start of Critical Code
>>>>>>>>>
>>>>>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>>>>>> Start a new session
>>>>>>>>> IF li_rc < 0 THEN // Session OK?
>>>>>>>>> oleruntimeerror lo_ex // NO=>Alloc
>>>>>>>>> Structure
>>>>>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>>>>>> CASE -1
>>>>>>>>> lo_ex.text = "Invalid Call: the argument is the Object property
>>>>>>>>> of a control"
>>>>>>>>> CASE -2
>>>>>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>>>>>> CASE -3
>>>>>>>>> lo_ex.text = "Excel session could not be created"
>>>>>>>>> CASE -4
>>>>>>>>> lo_ex.text = "Could not connect to Excel"
>>>>>>>>> CASE -9
>>>>>>>>> lo_ex.text = "Other error"
>>>>>>>>> CASE -15
>>>>>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>>>>>> CASE -16
>>>>>>>>> lo_ex.text = "Invalid argument"
>>>>>>>>> CASE Else
>>>>>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>>>>>> End Choose
>>>>>>>>> THROW lo_ex // Send an Error
>>>>>>>>> RETURN li_rc // Return to caller
>>>>>>>>> END IF
>>>>>>>>>
>>>>>>>>> // Start the real DW Blob Worksheet
>>>>>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>>>>>> Activate an OLE session
>>>>>>>>>
>>>>>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get
>>>>>>>>> Pointer to main ComandBar
>>>>>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get
>>>>>>>>> #WorkBooks
>>>>>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>>>>>> Controls in CB
>>>>>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>>>>>
>>>>>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>>>>>> controls
>>>>>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>>>>>> "Text" of Control
>>>>>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is
>>>>>>>>> this a Sensitive menu?
>>>>>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>>>>>> YES=>Disable it!
>>>>>>>>> END IF
>>>>>>>>> NEXT
>>>>>>>>>
>>>>>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>>>>>
>>>>>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>>>>>
>>>>>>>>> Finally // End of TRY
>>>>>>>>> lo_ole.Disconnectobject ( )
>>>>>>>>>
>>>>>>>>> End TRY
>>>>>>>>>
>>>>>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>>>>>
>>>>>>>>> Return li_rc // Return to caller
>>>>>>>>>
>>>>>>>>> -------------------------------------------------------------------
>>>>>>>>>
>>>>>>>>> HTH
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards ... Chris
>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>>>> news:4aba0378$1@forums-1-dub...
>>>>>>>>>> Hi, Chris
>>>>>>>>>>
>>>>>>>>>> I don't know if this would be of help to you, but... the
>>>>>>>>>> following macro for older Excel versions shows sample item
>>>>>>>>>> manipulation not using handles:
>>>>>>>>>>
>>>>>>>>>> Sub a()
>>>>>>>>>> ' Just for debug
>>>>>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>>>>>> ' Next
>>>>>>>>>>
>>>>>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>>>>>> For Each ctl2 In ctl.Controls
>>>>>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " &
>>>>>>>>>> ctl2.Caption)
>>>>>>>>>> ' Open, Save or Save As
>>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>>> ctl2.Enabled = False
>>>>>>>>>> End If
>>>>>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>>>>>> 'End If
>>>>>>>>>> Next ctl2
>>>>>>>>>> End If
>>>>>>>>>> Next ctl
>>>>>>>>>>
>>>>>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>>>>> ' Open, Save or Save As
>>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>>> ctl2.Enabled = False
>>>>>>>>>> End If
>>>>>>>>>> Next ctl2
>>>>>>>>>>
>>>>>>>>>> End Sub
>>>>>>>>>>
>>>>>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar
>>>>>>>>>> functionality (everything - menus and toolbars in Excel are
>>>>>>>>>> referred as CommandBars). This macro disables Open, Save and
>>>>>>>>>> SaveAs items separatedly in the menu bar and in the "Standard"
>>>>>>>>>> toolbar. The items themselves are referred by their IDs (Open =
>>>>>>>>>> 23, Save = 3, SaveAs = 748). However, I've no idea if the IDs or
>>>>>>>>>> the CommandBar object is the same for Excel 2007, either how to
>>>>>>>>>> convert this macro to a working PB OLE script sequence. Notice
>>>>>>>>>> that the items you want disabled may be configured by the user to
>>>>>>>>>> be shown in some other toolbars or submenus (via Tools -->
>>>>>>>>>> Customize ...... drag&drop). In this case the macro would be
>>>>>>>>>> cleared to universally traverse all the commandbars along with
>>>>>>>>>> their controls and find the desired items with these specific
>>>>>>>>>> IDs.
>>>>>>>>>>
>>>>>>>>>> Hope this helps a little
>>>>>>>>>>
>>>>>>>>>> Kind regards,
>>>>>>>>>> Ivaylo
>>>>>>>>>>
>>>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>>>>>> All;
>>>>>>>>>>>
>>>>>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>>>>>> disabled. I used to be able to do this in external applications
>>>>>>>>>>> by getting the handle to the menu objects and disabling them via
>>>>>>>>>>> PB script. However, in Excel 2003 and higher - it seems that the
>>>>>>>>>>> menu objects were replaced with user objects (grrr). So now, I
>>>>>>>>>>> seem to have no control on the menu items (that I know of).
>>>>>>>>>>>
>>>>>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>>>>>
>>>>>>>>>>> Any help would be appreciated!
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Regards ... Chris
>>>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Chris Pollach Posted on 2009-10-02 16:41:14.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub> <4ac0cfce@forums-1-dub> <4ac1f94f$1@forums-1-dub> <4ac22080@forums-1-dub> <4ac221c5@forums-1-dub> <4ac231cb$1@forums-1-dub> <4ac240cb@forums-1-dub> <4ac251ee@forums-1-dub> <4ac302c0@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 306
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac62d2a$1@forums-1-dub>
Date: 2 Oct 2009 09:41:14 -0700
X-Trace: forums-1-dub 1254501674 10.22.241.152 (2 Oct 2009 09:41:14 -0700)
X-Original-Trace: 2 Oct 2009 09:41:14 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9920
Article PK: 737060

Luckily for now ... all our 800+ users are on the same version of Excel. At
least .. that is what I am told. :-)

"Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
news:4ac302c0@forums-1-dub...
> That's pefect!
>
> Have you compared IDs between different Excel versions to ensure this
> works with older ones?
>
> :-)
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4ac251ee@forums-1-dub...
>>
>> Yes .. I have changed my code now to go after ID's.
>> It now works seamlessly on the English and French Excel versions. :-)
>>
>>
>>
>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>> news:4ac240cb@forums-1-dub...
>>> Laughed very often on some translations of the English menus ;-) ...
>>> being in Bulgaria
>>>
>>> So, stepping on the ID property if it's constantly corresponding to the
>>> same operation in different Excel versions would be a better option IMHO
>>>
>>> Good luck!
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4ac231cb$1@forums-1-dub...
>>>>
>>>> Yes ... being in Canada - I have the problem with English and French
>>>> versions of Excel! :-)
>>>>
>>>>
>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>> news:4ac221c5@forums-1-dub...
>>>>> Ah, forget to mention the possibility for the end-user to install
>>>>> Excel with non-English menus:
>>>>> "Neu", "Offnen", "Speichern" for German MS Office
>>>>>
>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>> news:4ac22080@forums-1-dub...
>>>>>> Have you explored the ID property of the looped controls? Are they
>>>>>> the same between different Excel versions?
>>>>>> I mean "open" command always having ID = 23, "save" command always
>>>>>> having ID = 3 etc.
>>>>>> If it's so, you might wish to rely on the ID, not on the Caption and
>>>>>> this way would natively trap the user-customized captions.
>>>>>>
>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>> news:4ac1f94f$1@forums-1-dub...
>>>>>>> Good points. My coding was just a prototype when I posted it. I have
>>>>>>> since refined it to loop through the various CommandBars and attach
>>>>>>> the problem generically. I did no think about the aspect of the user
>>>>>>> customizing Excel - in fact, I did not know that you could change
>>>>>>> the menu names. Thanks for that information. hopefully, my users
>>>>>>> will not do that (currently 800+ users on the system I am
>>>>>>> implementing this on).
>>>>>>>
>>>>>>>
>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>> news:4ac0cfce@forums-1-dub...
>>>>>>>> Good news for Excel 2007 users that CommandBar mechanizm still
>>>>>>>> works :-)
>>>>>>>>
>>>>>>>> Chris, I've just wanted to turn your attention to the constants you
>>>>>>>> use:
>>>>>>>> = CommandBars(1) - Can you be sure that index of 1 within the
>>>>>>>> CommandBars is always the main CommandBar?
>>>>>>>> = Caption property is not readonly - it CAN be customized, so these
>>>>>>>> words "open", "new" and "save" might not be properly located
>>>>>>>> = what about the customized toolbars (with the pictures)? This
>>>>>>>> script is supposed to manipulate Enabled state of the menu items,
>>>>>>>> but the toolbar items must be processed separatedly. And what if
>>>>>>>> your Excel installation has been customized by the user thus having
>>>>>>>> more than one instance of these "open", "new" and "save" commands
>>>>>>>> in the menus or in the toolbars?
>>>>>>>>
>>>>>>>> Just a thoughts to consider
>>>>>>>>
>>>>>>>> Best regards,
>>>>>>>> Ivaylo (not Ivan ;-))
>>>>>>>>
>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>> news:4ac0b36d@forums-1-dub...
>>>>>>>>> Thanks Ivan;
>>>>>>>>>
>>>>>>>>> That plus a little more reading and I have it working. :-)
>>>>>>>>>
>>>>>>>>> For those following in my footsteps ... you need to start an OLE
>>>>>>>>> session with Excel along with the DW's OLEActivate session. This
>>>>>>>>> allows you to control Excel. You will also need to get a handle to
>>>>>>>>> the various objects inside Excel - as follows:
>>>>>>>>>
>>>>>>>>> -------------------------------------------------------------------------------------------
>>>>>>>>>
>>>>>>>>> n_oo lo_ole // OLE Container
>>>>>>>>> OLEObject lo_ole_wb // WorkBook Pointer
>>>>>>>>> OLEObject lo_ole_ws // WorkSheet Pointer
>>>>>>>>> OLEObject lo_ole_cb // CommandBar Pointer
>>>>>>>>> OLEObject lo_ole_mb //
>>>>>>>>> OLEObject lo_ole_ct // Control Pointer
>>>>>>>>>
>>>>>>>>> lo_ole = CREATE n_oo // Instantiate Main OLE
>>>>>>>>> component
>>>>>>>>>
>>>>>>>>> TRY // Start of Critical Code
>>>>>>>>>
>>>>>>>>> li_rc= lo_ole.ConnectToNewObject ("excel.application") //
>>>>>>>>> Start a new session
>>>>>>>>> IF li_rc < 0 THEN // Session OK?
>>>>>>>>> oleruntimeerror lo_ex // NO=>Alloc
>>>>>>>>> Structure
>>>>>>>>> lo_ex = create oleruntimeerror // Instantiate it
>>>>>>>>> lo_ex.number = li_rc // Load ReturnCode
>>>>>>>>> lo_ex.objectname = THIS.Classname( ) // Get My name
>>>>>>>>> Choose Case lo_ex.number // Decipher error ...
>>>>>>>>> CASE -1
>>>>>>>>> lo_ex.text = "Invalid Call: the argument is the Object property
>>>>>>>>> of a control"
>>>>>>>>> CASE -2
>>>>>>>>> lo_ex.text = "Excel name not found on this computer"
>>>>>>>>> CASE -3
>>>>>>>>> lo_ex.text = "Excel session could not be created"
>>>>>>>>> CASE -4
>>>>>>>>> lo_ex.text = "Could not connect to Excel"
>>>>>>>>> CASE -9
>>>>>>>>> lo_ex.text = "Other error"
>>>>>>>>> CASE -15
>>>>>>>>> lo_ex.text = "EXCEL is not loaded on this computer"
>>>>>>>>> CASE -16
>>>>>>>>> lo_ex.text = "Invalid argument"
>>>>>>>>> CASE Else
>>>>>>>>> lo_ex.text = "A connection to Excel could not be made"
>>>>>>>>> End Choose
>>>>>>>>> THROW lo_ex // Send an Error
>>>>>>>>> RETURN li_rc // Return to caller
>>>>>>>>> END IF
>>>>>>>>>
>>>>>>>>> // Start the real DW Blob Worksheet
>>>>>>>>> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) //
>>>>>>>>> Activate an OLE session
>>>>>>>>>
>>>>>>>>> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get
>>>>>>>>> Pointer to main ComandBar
>>>>>>>>> lo_ole_wb = lo_ole.Application.ActiveWorkBook
>>>>>>>>> li_count = lo_ole_wb.WorkSheets.Count // Get
>>>>>>>>> #WorkBooks
>>>>>>>>> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
>>>>>>>>> Controls in CB
>>>>>>>>> li_count = lo_ole_ct.Controls.Count // Get # controls
>>>>>>>>>
>>>>>>>>> FOR li_loop = 1 to li_count // Loop thru Menu
>>>>>>>>> controls
>>>>>>>>> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get
>>>>>>>>> "Text" of Control
>>>>>>>>> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
>>>>>>>>> Pos (Lower (ls_title) , "open", 1) > 0 OR &
>>>>>>>>> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is
>>>>>>>>> this a Sensitive menu?
>>>>>>>>> lo_ole_ct.Controls(li_loop).enabled = FALSE //
>>>>>>>>> YES=>Disable it!
>>>>>>>>> END IF
>>>>>>>>> NEXT
>>>>>>>>>
>>>>>>>>> li_rc = +1 // Set RC to "OK"!
>>>>>>>>>
>>>>>>>>> Catch ( RunTimeError lo_rte ) // Error?
>>>>>>>>> li_rc = -1 // YES=>Set RC to "bad"
>>>>>>>>> MessageBox ("OLE Start Session Error!", lo_rte.text)
>>>>>>>>>
>>>>>>>>> Finally // End of TRY
>>>>>>>>> lo_ole.Disconnectobject ( )
>>>>>>>>>
>>>>>>>>> End TRY
>>>>>>>>>
>>>>>>>>> Destroy (lo_ole) // Clean up OLE object
>>>>>>>>>
>>>>>>>>> Return li_rc // Return to caller
>>>>>>>>>
>>>>>>>>> -------------------------------------------------------------------
>>>>>>>>>
>>>>>>>>> HTH
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards ... Chris
>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
>>>>>>>>> news:4aba0378$1@forums-1-dub...
>>>>>>>>>> Hi, Chris
>>>>>>>>>>
>>>>>>>>>> I don't know if this would be of help to you, but... the
>>>>>>>>>> following macro for older Excel versions shows sample item
>>>>>>>>>> manipulation not using handles:
>>>>>>>>>>
>>>>>>>>>> Sub a()
>>>>>>>>>> ' Just for debug
>>>>>>>>>> ' For Each cbar In CommandBars.ActiveMenuBar
>>>>>>>>>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>>>>>>>>>> ' Next
>>>>>>>>>>
>>>>>>>>>> ' Find and disable 3 operations in the menu bar
>>>>>>>>>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>>>>>>>>>> If ctl.ID = 30002 Then 'File menu
>>>>>>>>>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>>>>>>>>>> For Each ctl2 In ctl.Controls
>>>>>>>>>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " &
>>>>>>>>>> ctl2.Caption)
>>>>>>>>>> ' Open, Save or Save As
>>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>>> ctl2.Enabled = False
>>>>>>>>>> End If
>>>>>>>>>> 'If ctl2.Caption = "&Save" Then
>>>>>>>>>> 'ctl2.Caption = "&Save for Ivo"
>>>>>>>>>> 'End If
>>>>>>>>>> Next ctl2
>>>>>>>>>> End If
>>>>>>>>>> Next ctl
>>>>>>>>>>
>>>>>>>>>> ' Find and disable 3 operations in the Standard toolbar
>>>>>>>>>> For Each ctl2 In CommandBars("Standard").Controls
>>>>>>>>>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>>>>>>>>>> ' Open, Save or Save As
>>>>>>>>>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>>>>>>>>>> MsgBox ("Will be disabled")
>>>>>>>>>> ctl2.Enabled = False
>>>>>>>>>> End If
>>>>>>>>>> Next ctl2
>>>>>>>>>>
>>>>>>>>>> End Sub
>>>>>>>>>>
>>>>>>>>>> Maybe it'll give you a clue how to use VBA's CommandBar
>>>>>>>>>> functionality (everything - menus and toolbars in Excel are
>>>>>>>>>> referred as CommandBars). This macro disables Open, Save and
>>>>>>>>>> SaveAs items separatedly in the menu bar and in the "Standard"
>>>>>>>>>> toolbar. The items themselves are referred by their IDs (Open =
>>>>>>>>>> 23, Save = 3, SaveAs = 748). However, I've no idea if the IDs or
>>>>>>>>>> the CommandBar object is the same for Excel 2007, either how to
>>>>>>>>>> convert this macro to a working PB OLE script sequence. Notice
>>>>>>>>>> that the items you want disabled may be configured by the user to
>>>>>>>>>> be shown in some other toolbars or submenus (via Tools -->
>>>>>>>>>> Customize ...... drag&drop). In this case the macro would be
>>>>>>>>>> cleared to universally traverse all the commandbars along with
>>>>>>>>>> their controls and find the desired items with these specific
>>>>>>>>>> IDs.
>>>>>>>>>>
>>>>>>>>>> Hope this helps a little
>>>>>>>>>>
>>>>>>>>>> Kind regards,
>>>>>>>>>> Ivaylo
>>>>>>>>>>
>>>>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>>>>> news:4ab91f7f$1@forums-1-dub...
>>>>>>>>>>> All;
>>>>>>>>>>>
>>>>>>>>>>> I have an OLE DataWindow session started with Excel. For user
>>>>>>>>>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>>>>>>>>>> disabled. I used to be able to do this in external applications
>>>>>>>>>>> by getting the handle to the menu objects and disabling them via
>>>>>>>>>>> PB script. However, in Excel 2003 and higher - it seems that the
>>>>>>>>>>> menu objects were replaced with user objects (grrr). So now, I
>>>>>>>>>>> seem to have no control on the menu items (that I know of).
>>>>>>>>>>>
>>>>>>>>>>> Soooo.... the "big question" is whether or not I can
>>>>>>>>>>> enable/disable the Excel menus if I get a secondary OLE session
>>>>>>>>>>> active over to Excel and then issue an ______ OLE command?
>>>>>>>>>>>
>>>>>>>>>>> Any help would be appreciated!
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Regards ... Chris
>>>>>>>>>>> ISUG - NA RUG Director
>>>>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Chris Pollach Posted on 2009-09-28 13:04:27.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.objects
References: <4ab91f7f$1@forums-1-dub> <4aba0378$1@forums-1-dub> <4ac0b36d@forums-1-dub>
Subject: Re: OLE with Excel
Lines: 202
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac0b45b$1@forums-1-dub>
Date: 28 Sep 2009 06:04:27 -0700
X-Trace: forums-1-dub 1254143067 10.22.241.152 (28 Sep 2009 06:04:27 -0700)
X-Original-Trace: 28 Sep 2009 06:04:27 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9906
Article PK: 737035

BTW: the object "N_OO" is a PFC component and descendant of an OLEObject
container.

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4ac0b36d@forums-1-dub...
> Thanks Ivan;
>
> That plus a little more reading and I have it working. :-)
>
> For those following in my footsteps ... you need to start an OLE session
> with Excel along with the DW's OLEActivate session. This allows you to
> control Excel. You will also need to get a handle to the various objects
> inside Excel - as follows:
>
> -------------------------------------------------------------------------------------------
>
> n_oo lo_ole // OLE Container
> OLEObject lo_ole_wb // WorkBook Pointer
> OLEObject lo_ole_ws // WorkSheet Pointer
> OLEObject lo_ole_cb // CommandBar Pointer
> OLEObject lo_ole_mb //
> OLEObject lo_ole_ct // Control Pointer
>
> lo_ole = CREATE n_oo // Instantiate Main OLE component
>
> TRY // Start of Critical Code
>
> li_rc= lo_ole.ConnectToNewObject ("excel.application") // Start a
> new session
> IF li_rc < 0 THEN // Session OK?
> oleruntimeerror lo_ex // NO=>Alloc Structure
> lo_ex = create oleruntimeerror // Instantiate it
> lo_ex.number = li_rc // Load ReturnCode
> lo_ex.objectname = THIS.Classname( ) // Get My name
> Choose Case lo_ex.number // Decipher error ...
> CASE -1
> lo_ex.text = "Invalid Call: the argument is the Object property of a
> control"
> CASE -2
> lo_ex.text = "Excel name not found on this computer"
> CASE -3
> lo_ex.text = "Excel session could not be created"
> CASE -4
> lo_ex.text = "Could not connect to Excel"
> CASE -9
> lo_ex.text = "Other error"
> CASE -15
> lo_ex.text = "EXCEL is not loaded on this computer"
> CASE -16
> lo_ex.text = "Invalid argument"
> CASE Else
> lo_ex.text = "A connection to Excel could not be made"
> End Choose
> THROW lo_ex // Send an Error
> RETURN li_rc // Return to caller
> END IF
>
> // Start the real DW Blob Worksheet
> li_rc = ao_dc.OleActivate ( 1, "cads_worksheet", 0) // Activate
> an OLE session
>
> lo_ole_cb = lo_ole.Application.CommandBars(1) // Get Pointer to
> main ComandBar
> lo_ole_wb = lo_ole.Application.ActiveWorkBook
> li_count = lo_ole_wb.WorkSheets.Count // Get #WorkBooks
> lo_ole_ct = lo_ole_cb.Controls(1) // Get Pointer to
> Controls in CB
> li_count = lo_ole_ct.Controls.Count // Get # controls
>
> FOR li_loop = 1 to li_count // Loop thru Menu controls
> ls_title = lo_ole_ct.Controls(li_loop).Caption // Get "Text"
> of Control
> IF Pos (Lower (ls_title) , "new", 1) > 0 OR &
> Pos (Lower (ls_title) , "open", 1) > 0 OR &
> Pos (Lower (ls_title) , "save", 1) > 0 THEN // Is this a
> Sensitive menu?
> lo_ole_ct.Controls(li_loop).enabled = FALSE // YES=>Disable
> it!
> END IF
> NEXT
>
> li_rc = +1 // Set RC to "OK"!
>
> Catch ( RunTimeError lo_rte ) // Error?
> li_rc = -1 // YES=>Set RC to "bad"
> MessageBox ("OLE Start Session Error!", lo_rte.text)
>
> Finally // End of TRY
> lo_ole.Disconnectobject ( )
>
> End TRY
>
> Destroy (lo_ole) // Clean up OLE object
>
> Return li_rc // Return to caller
>
> -------------------------------------------------------------------
>
> HTH
>
> --
> Regards ... Chris
> ISUG - NA RUG Director
> http://chrispollach.pbdjmagazine.com
>
>
> "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com> wrote in message
> news:4aba0378$1@forums-1-dub...
>> Hi, Chris
>>
>> I don't know if this would be of help to you, but... the following macro
>> for older Excel versions shows sample item manipulation not using
>> handles:
>>
>> Sub a()
>> ' Just for debug
>> ' For Each cbar In CommandBars.ActiveMenuBar
>> ' MsgBox (cbar.Name & " : " & cbar.NameLocal)
>> ' Next
>>
>> ' Find and disable 3 operations in the menu bar
>> For Each ctl In CommandBars("Worksheet Menu Bar").Controls
>> If ctl.ID = 30002 Then 'File menu
>> MsgBox (CStr(ctl.ID) & " : " & ctl.Caption)
>> For Each ctl2 In ctl.Controls
>> MsgBox ("File --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>> ' Open, Save or Save As
>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>> MsgBox ("Will be disabled")
>> ctl2.Enabled = False
>> End If
>> 'If ctl2.Caption = "&Save" Then
>> 'ctl2.Caption = "&Save for Ivo"
>> 'End If
>> Next ctl2
>> End If
>> Next ctl
>>
>> ' Find and disable 3 operations in the Standard toolbar
>> For Each ctl2 In CommandBars("Standard").Controls
>> MsgBox ("Standard --> " & CStr(ctl2.ID) & " : " & ctl2.Caption)
>> ' Open, Save or Save As
>> If ctl2.ID = 23 Or ctl2.ID = 3 Or ctl2.ID = 748 Then
>> MsgBox ("Will be disabled")
>> ctl2.Enabled = False
>> End If
>> Next ctl2
>>
>> End Sub
>>
>> Maybe it'll give you a clue how to use VBA's CommandBar functionality
>> (everything - menus and toolbars in Excel are referred as CommandBars).
>> This macro disables Open, Save and SaveAs items separatedly in the menu
>> bar and in the "Standard" toolbar. The items themselves are referred by
>> their IDs (Open = 23, Save = 3, SaveAs = 748). However, I've no idea if
>> the IDs or the CommandBar object is the same for Excel 2007, either how
>> to convert this macro to a working PB OLE script sequence. Notice that
>> the items you want disabled may be configured by the user to be shown in
>> some other toolbars or submenus (via Tools --> Customize ......
>> drag&drop). In this case the macro would be cleared to universally
>> traverse all the commandbars along with their controls and find the
>> desired items with these specific IDs.
>>
>> Hope this helps a little
>>
>> Kind regards,
>> Ivaylo
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4ab91f7f$1@forums-1-dub...
>>> All;
>>>
>>> I have an OLE DataWindow session started with Excel. For user
>>> logistical purposes, they need the File=>Open/Save/SaveAs menus
>>> disabled. I used to be able to do this in external applications by
>>> getting the handle to the menu objects and disabling them via PB script.
>>> However, in Excel 2003 and higher - it seems that the menu objects were
>>> replaced with user objects (grrr). So now, I seem to have no control on
>>> the menu items (that I know of).
>>>
>>> Soooo.... the "big question" is whether or not I can enable/disable
>>> the Excel menus if I get a secondary OLE session active over to Excel
>>> and then issue an ______ OLE command?
>>>
>>> Any help would be appreciated!
>>>
>>> --
>>> Regards ... Chris
>>> ISUG - NA RUG Director
>>> http://chrispollach.pbdjmagazine.com
>>>
>>>
>>>
>>
>>
>
>


Wheeley Posted on 2009-09-24 06:00:16.0Z
From: Wheeley <wheeley@ix.netcom.com>
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: OLE with Excel
Message-ID: <qj2mb59270j9cqpebkr9s8c768sv84j468@4ax.com>
References: <4ab91f7f$1@forums-1-dub>
X-Newsreader: Forte Agent 5.00/32.1171
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 Sep 2009 23:00:16 -0700
X-Trace: forums-1-dub 1253772016 10.22.241.152 (23 Sep 2009 23:00:16 -0700)
X-Original-Trace: 23 Sep 2009 23:00:16 -0700, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9904
Article PK: 737036

On 22 Sep 2009 12:03:27 -0700, "Chris Pollach"
<cpollach@travel-net.com> wrote:

For 2003 you could do this. BUT for Excel 2007 you will be out of
luck. The new ribbon interface doesn't have the hooks to allow this.

Wheeley

>All;
>
> I have an OLE DataWindow session started with Excel. For user logistical
>purposes, they need the File=>Open/Save/SaveAs menus disabled. I used to be
>able to do this in external applications by getting the handle to the menu
>objects and disabling them via PB script. However, in Excel 2003 and
>higher - it seems that the menu objects were replaced with user objects
>(grrr). So now, I seem to have no control on the menu items (that I know
>of).
>
> Soooo.... the "big question" is whether or not I can enable/disable the
>Excel menus if I get a secondary OLE session active over to Excel and then
>issue an ______ OLE command?
>
>Any help would be appreciated!