Excel and OLE
Dave Francis
I'm pumping data into an Excel file, but this Excel file has macros and
security is set to medium, so a warning dialog appears asking if macros can
be run.
Does anyone know how I can answer Yes to the dialog, or set the security
level to Low before my session and restore it to medium after the session?
I've tried VB help but I'm not sure I'm looking for the right key words ;o)
TIA
Dave Francis
Geoff Schaller
You can't - this is precisely why such security exists or it wouldn't be
much of a security now would it? <g>
But there are other things you can do on the target machine. You can
digitally sign your templates, macros and files but then you need to
install certificates. Alternately the user needs to set the security
level for themselves.
If you set the Excel security to only trust signed macros and then you
set your certificate as a trusted publisher, you can overcome the
warning but it is designed to prevent being turned off otherwise.
Geoff
"Dave Francis" <dave_IsASp...@suilven.com> wrote in message
news:dave_IsASp...@suilven.com:
Willie Moore
You can actually answer yes to that dialog. what you will have to do find
out the handle to the excel window, then post the message to it's queue.
Paul Piko did a really good session a DevconUSA last year on controling
windows applications. I believe that he also did it at the Aussie Devcon.
Regards,
Willie
"Dave Francis" <dave_IsASp...@suilven.com> wrote in message
news:3dd05tF...@individual.net...
Robert van der Hulst
On Thu, 28 Apr 2005, at 21:42:54 [GMT +0100] (which was 22:42 where I live)
you wrote about: 'Excel and OLE'
You have to set the registry key:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security
Create or Update the Value "Level" (type REG_DWORD )
1 = Low, 2 = Medium, 3 = High, 4 = Very High
--
Robert van der Hulst
AKA Mr. Data
Vo2Jet & Vo2Ado Support
VO Development Team
www.sas-software.nl
mrdata...@sas-software.com
Geoff Schaller
I'd be quite interested to see if what Paul showed can apply to this
particular window. Whilst in theory its supposed to be possible, my
understanding from MS literature is more along the lines that this and
other security windows will not allow such access for the very reason
they exist. ...to provide incontrovertible security.
If you are are able to circumvent this dialog I would be very
interested. I have some use for it <g>. But equally, it would be a
significant flaw in the MS Office security model and MS would be very
interested!
Cheers,
Geoff
"Willie Moore" <wil...@wmconsulting.com> wrote in message
news:wil...@wmconsulting.com:
Gary Stark
> Dave,
>
> You can't - this is precisely why such security exists or it wouldn't be
> much of a security now would it? <g>
Sorry, but you can. This is really so easy to do ... <yawn>
--
g.
Gary Stark
gstark @ RedbacksWeb . com
http://www.Redbacksweb.com
Gary Stark
> Hi all,
>
> I'm pumping data into an Excel file, but this Excel file has macros and
> security is set to medium, so a warning dialog appears asking if macros can
> be run.
>
> Does anyone know how I can answer Yes to the dialog, or set the security
> level to Low before my session and restore it to medium after the session?
>
Dave,
Look at the CD Burning sample in your sample directory.
It contains some screen scraping code that should let you handle this
fairly painlessly.
You'll need a utility like Spy++ to help you identify the window
components that will isolate the task that you're wanting to perform,
but that's pretty menial as well.
Gary Stark
Geoff Schaller wrote:
> Willie,
>
> I'd be quite interested to see if what Paul showed can apply to this
> particular window. Whilst in theory its supposed to be possible, my
> understanding from MS literature is more along the lines that this and
> other security windows will not allow such access for the very reason
> they exist. ...to provide incontrovertible security.
>
> If you are are able to circumvent this dialog I would be very
> interested. I have some use for it <g>. But equally, it would be a
> significant flaw in the MS Office security model and MS would be very
> interested!
As easy as falling off a bloody log.
The point is that you do not circumvent the dialog, but respond to it
with code.
I don't have the tools with me here, but it wouldn't be more than about
15 minutes' work, using the CD Burner sample plus Spy++ to find the keys
to the window and controls that you want to hit.
Hell, I think that even you could manage to do this one. :)
Geoff Schaller
That's nice. If its such a yawn and so easy for you perhaps you'd like
to explain how? It might be a little more useful to us all rather than a
sarcastic comment.
Geoff
"Gary Stark" <Bogus...@Yahoo.com> wrote in message
news:Bogus...@Yahoo.com:
Gary Stark
All that you need to know to begin code this is in
METHOD pbBegin( ) CLASS dlgCDBurner
of the CD Burner example.
Use Spy++ to identify the correct window and and the correct button to
hit within that window, and it's done!
Is that easy enough for you?
Dave Francis
1 Geoff, Not my workbook nor macros, so adding certificates would be
dangerous. These are a great examples of what not to do with Excel - 8
separate 4 Mbyte files with 5 other support files - all intrically linked.
They, in turn, consolidate with similar sets from other sites at the
corporate level. It's one big timebomb waiting to go Phut! at the end of one
month. The less I touch it the better!
2 Robert, Never thought of the registry :o)
3 Gary, I'm going to try the reg fix first, bbut there will be other
situations where the sample will be very useful, so I'll see if I can get my
head around that.
Thanks again,
Dave Francis
"Gary Stark" <Bogus...@Yahoo.com> wrote in message
news:3ddqedF...@individual.net...
Geoff Schaller
We spent quite a bit of time trying to get a "live" handle to one of
these windows and then sending it a message and it didn't work. We
contacted Microsoft Support over the issue and basically they said that
the dialog is locked down precisely so program control cannot be exerted
over it. Now this was about 10 months ago and I'm not saying we did
everything 100% correctly but that was the outcome at the time. I mean
what's the point of security windows if they can be so eaily controlled
and whisked away?
So if it really is a 15 minute job I'll wait till someone does this and
publishes the results <g>.
Meantime, installing a certificate and trusting the publisher does work
so we'll keep to this path for now.
Geoff
Tony Holland
the expert here, perhaps he would expend 15 minutes of his time and present
us with a sample app that performs this action for us to view. I know for
one, that I would be grateful to see this.
Regards
Tony Holland
Gary wrote
> I don't have the tools with me here, but it wouldn't be more than about
> 15 minutes' work, using the CD Burner sample plus Spy++ to find the keys
> to the window and controls that you want to hit.
>
> Hell, I think that even you could manage to do this one. :)
Gary Stark
functionality that you've told us can't be provided. I'd truly hate to
disappoint you and prove you wrong.
Tony,
Tony Holland wrote:
> This is certainly a useful procedure to master, and as Gary appears to be
> the expert here, perhaps he would expend 15 minutes of his time and present
> us with a sample app that performs this action for us to view. I know for
> one, that I would be grateful to see this.
I make no claims to be an expert.
However, I had a few minutes this morning to play with this, and here is
some code that kills the MS Outlook security warning dialog.
The procedure for dealing with Excel should be pretty much the same.
You'll need to use Spy++ to correctly identify the window and button
compenents that you're wanting to hit.
FWIW, it took about 20 minutes to get this up and running - I needed to
execute the code in a separate thread because the Outlook dialog was
modal, and thus the call that forces it to pop up will wait until
there's a response.
If my code was left inline, it simply wouldn't execute unless and until
the MSO security dialog was dismissed by a user, which kind of defeats
the purpose. :)
Prerequisites:
I've patched this code into one of the standard ComSDK Apps that Frans
and Ed have given us, so first of all, install ComSDK.
Then do what you need to do to get the VO2Outlook InboxExplorer sample
application running.
When you try to run this app, and if you have later versions of MSO
(2003, for instance) installed, you'll get the MSO security dialog
popping up prior to the VO2Outlook InboxExplorer popping up its window
and running interference.
If you manually nominate that you want to permit the application to
access MSO, you can then nominate a period of time (1 minute, 2 minutes,
etc) and press the "Yes" button to make it all happen.
What the following code does is automate that procedure. This will be
useful where, for instance, your user might not have rights to play in
the registry sandpit.
We're going to be placing our hooks into the VO2Outlook 3 GUI app, which
is a library that the VO2Outlook InboxExplorer uses.
Open the TS_SimpleInboxExplorer module, and in your text editor, open
the SetupInbox() method of TS_SimpleInboxExplorer.
Add the following declaration somewhere near the start of this method:
LOCAL dwID AS DWORD
After the line
FOR i := 1 UPTO nCount
Add
CreateVOThread( NULL, 0, @Outbreak(), NULL, 0, @dwID )
And then save and close this entity. This line sets up the application
to call the new code in its own thread which can dismiss any security
dialog.
Create a new module; I've called it Functions; you may call it whatever
you want.
Open this new module in your text editor and paste the following code
into it, correcting any word wrapping that may occur during the copy and
paste procedures.
=================================
FUNCTION Yield() AS LOGIC
ApplicationExec( EXECWHILEEVENT )
RETURN TRUE
FUNCTION Outbreak()
// Copyright (c) 2005 Gary Stark
// gst...@Redbacksweb.com
LOCAL hWndOther AS PTR
LOCAL hWndYesButton AS PTR
LOCAL hWndCheckBox AS PTR
LOCAL hWndCombo AS PTR
LOCAL nLoops AS DWORD
LOCAL nMaxLoops AS DWORD
LOCAL pRect IS _WINRect
LOCAL lLox, lHiy AS LONG
nLoops := 0
nMaxLoops := 25
hWndOther := FindWindow(Null_Psz, PSZ("Microsoft Office Outlook"))
WHILE hWndOther == NULL_PTR .and. nLoops < nMaxLoops
Sleep( 500 ) // wait for the dialog
hWndOther := FindWindow( Null_Psz, PSZ( "Microsoft Office
Outlook" ) )
nLoops += 1
Yield()
END
IF !hWndOther == NULL_PTR
// we have a dialog
hWndCheckBox := FindWindowEx( hWndOther, NULL_PTR, String2Psz(
"Button" ), String2Psz( "&Allow access for" ) )
hWndCombo := FindWindowEx( hWndOther, NULL_PTR, String2Psz(
"ComboBox" ), String2Psz( "" ) )
hWndYesButton := FindWindowEx( hWndOther, NULL_PTR, String2Psz(
"Button" ), String2Psz( "Yes" ) )
IF !hWndOther == NULL_PTR
SetForegroundWindow( hWndOther )
Sleep( 500 ) // wait
GetWindowRect( hWndYesButton, @pRect )
PostMessage( hWndYesButton, WM_CHAR, Asc( "A" ), 1L )
// Uncomment the next two lines to set the Outlook
// timer to 2 minutes
// PostMessage( hWndCombo, WM_CHAR, Asc( "2" ), 1L )
lLox := pRect.left + 30
lHiy := pRect.Top + 10
SetCursorPos( lLox, lHiy )
mouse_event( MOUSEEVENTF_LEFTDOWN, lLox, lHiy,0, 0 )
mouse_event( MOUSEEVENTF_LEFTUP, lLox, lHiy,0, 0 )
END
ELSEIF nLoops >= nMaxLoops
errorbox{ , "MS Outlook failed to start" }:SHow()
END
IF nLoops < nMaxLoops
IF Check4End( hWndYesButton )
// All is ok
ELSE
ExitVOThread(0)
END
ELSE
ExitVOThread(0)
END
FUNCTION Check4End( hWndYesButton )
// Copyright (c) 2005 Gary Stark
// gst...@Redbacksweb.com
LOCAL hWndOther AS PTR
LOCAL i AS DWORD
FOR i := 1 TO 15
Sleep( 500 ) // wait for the dialog
Yield()
hWndOther := FindWindow( Null_Psz, PSZ( "Microsoft Office
Outlook" ) )
IF hWndOther == NULL_PTR
EXIT
END
Yield()
NEXT
IF hWndOther == NULL_PTR
// dialog has closed; so can we.
ExitVOThread(0)
END
RETURN i <= 15
=================================
The significant code lives in the Outbreak function. I've placed a few
sleep() calls in there to slow it down so that you can see the code in
action; they can be reduced to minimal values (or probably removed) if
you like.
Enjoy.
>
> Regards
>
> Tony Holland
>
> Gary wrote
>
> > I don't have the tools with me here, but it wouldn't be more than about
> > 15 minutes' work, using the CD Burner sample plus Spy++ to find the keys
> > to the window and controls that you want to hit.
> >
> > Hell, I think that even you could manage to do this one. :)
> > g.
> > Gary Stark
> > gstark @ RedbacksWeb . com
> > http://www.Redbacksweb.com
>
>
>
--
Ed Richard
I really don't understand the origin of the problem though, so Dave's app
will have the ability to make changes in the registry? well in that case
he's in control isn't he? Sounds like the right solution here. Did that work
Dave?
As for Outlook, for everyone who's lurking:
Are you guys also aware of the Mapilab tool that allows a user to allow
access to your application?
http://www.mapilab.com/outlook/security/
I've been using that and although there are Outlook add-ins that conflict
with it, in a normal Outlook environment it seems to work nicely.
Also when in an Exchange environment the adminsitrator could control the
security setting.
http://office.microsoft.com/en-us/assistance/HA011402931033.aspx
Here's a page with some more references:
http://www.mapilab.com/articles/vb_outlook_security.html
This lets the user or app-manager decide which is probably a lot better than
using code hacks. We need to start thinking this way, when moving towards
.net it's time to get serious about security settings and what your
applications are allowed to do and what not. Certificates and signing are
definitely the only way in the (near?) future.
Ed
"Gary Stark" <Bogus...@Yahoo.com> wrote in message
news:3dtabl...@individual.net...
Gary Stark
Ed Richard wrote:
> Hm, cool stuff Gary, I don't like hacks like this, but if it works well...
I may not like having to use a hack like this either, but sometimes you
don't have a choice.
>
> I really don't understand the origin of the problem though, so Dave's app
> will have the ability to make changes in the registry?
I think that's what Dave's wanting to try, but as you know, there are
many circumstances where systems will be locked down and access to the
registry isn't permitted.
In this case, just let Outlook do it's thing, but let this little piece
of code handle Outlook's interference in the client's business needs. :)
--
g.
Gary Stark
gst...@RedbacksWeb.com
http://www.RedbacksWeb.com
Dave Francis
Robert's pointer to the registry hack worked well for me. But overall, I
agree with your security strategy. In my case, I was working a "secured" PC
(no user has access), so I have full access rights. I would be nervous doing
anything like this on a user PC because the Excel system I was writing to,
was like a house of playing cards - not supported and very wobbly (hey, a
bit like me ;o) . If it all goes wrong, how do I prove it wasn't me? More
importantly, how do I help get the user back up and running? On this PC, at
least I can force backups, so I have a way back from disasters.
Not a fan of MS's security policy which seems to go like this...
1. Allow everyone to do everything.
2. Panic and buy big padlocks and sprinkle them everywhere.
3. Leave keys under the door mat for those who know where to look.
Trouble is, we all look under the mat. I wonder how much .net will help this
before it also starts to decay. Entropy is powerful stuff - gets us all one
day.
Dave Francis
"Ed Richard" <E...@wss-ed.nl> wrote in message
news:d5cmqt$lav$1...@reader08.wxs.nl...
Ed Richard
It seems to me that MS is pretty serious about security nowadays and they
are doing what they can to lock down by default. XP SP2 and 2003 Srvr (and
SP1) demonstrates that. Sure there's keys under the doormat, but only those
that have been there, I don't see any new ones. Or can you give me examples?
In a managed code world they have provided lots of tools and appropriate
access and security mechanism to deal with it. Trouble is, having to deal
with it does not make it easier for the user and for the developer and it is
certainly something we need to address.
Anyway, awareness is probably the most important part of any strategy so we
have a task ahead of us make our software robust and the users aware of the
security issues they have to deal with. If anyone leaves keys, they do as
far as I can tell... How many client do you have where all users have the
same password?
Ed
"Dave Francis" <dave_IsASp...@suilven.com> wrote in message
news:3du82b...@individual.net...
Dave Francis
> It seems to me that MS is pretty serious about security nowadays and they
Yes, I think was looking over my shoulder with hindsight, so it was not
totally fair :o)
> How many client do you have where all users have the
> same password?
Oh lots! But of all the damage I have seen done to computer systems, 99%+
has been done from the inside by people (users, admin, management) who did
not intend to do damage. That's why I said Entropy gets us all in the end.
We live in a real World where we all have to get things done, and we all
choose the easiest path to achieve that. Sometimes that means a key under
the mat.
I don't pretend to know the answers, I just try and "expect the unexpected".
I don't do that by not taking risks, just by making sure that I spot
problems and have a way of fixing them.
I used to climb. The joy is in taking risks - the sanity is in roping
yourself and your partner so the consequences of the unexpected are not
serious.
Dave
"Ed Richard" <E...@wss-ed.nl> wrote in message
news:d5cqmr$fvn$1...@reader13.wxs.nl...