Mock that App

Topic

New Reply

Is there any way to make excel look/behave like an entirely different application?

Ideally, my user would open the file I sent them, (in excel format) and it would size the application window down to the size of the userform, hide all toolbars, and follow the position of the open userform, should the user move it.

If the user opens another excel file, as opposed to using the current instance of excel, the system would open another instance of excel to display the information…

Can this be done?

Thanks!

Reply | Quote

Replies

Drk,

Yes you can do this – but i probably won’t have the answers to all your questions. Here’s how I would approach it.

1) you hide all the toolbars
2) you change the title (application.caption??)
3) instead of a userform i think you’re better off using fields on a spreadsheet page where (for instance) the cells you want to look like input boxes are left white, the rest of the sheet is coloured a nice pretty colour, throw in the following code

With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With

hopefully now you can resize the window to the size you want and the overall effect is somewhere close to what you want.

4) as for a different instance of excel for any other spreadsheet, it again might be easier to put code in the workbook open event of MockApp.xls to open another instance of excel as you open it – what I find when I’m running two instances is that any file I click on through explorer will open in the most recently invoked instance.

5) to sum up, yes what you want can be done but if you take the route above, be careful to restore all settings on closing MockApp.xls. There’s nothing worse than opening excel to find that you have no toolbars and can’t do anything with it. I used to have a workbook that had startup code to restore settings like these for when I screwed up really badly and it came in handy more than once.

HTH

Brooke

Reply | Quote

Boy I don’t like the sound of that..

I have a Very impressive userform i’ve nursed to health from scratch… (with the help of all the fine folks here, of course…)

The biggest hurdle is making explorer ‘ignore’ my open version of excel… whether it was the most recently active or not, I want every other excel spreadsheet to open in a seperate instance of excel…

Thanks!

Reply | Quote

Drk,

I’m not sure its actually as bad as it sounds. But, if you can resize the application to fit a worksheet, then you must be able to resize it to fit a userform. That means you get to keep the userform and don’t need to rework it.

The separate instance is more of a problem – as I said, this can be done manually but it would appear you need this to happen programatically. I’ll try and dig something up – don’t hold your breath – but hopefully someone else has done something like this already and will beat me to it or you may end up waiting a long time!

Brooke

Reply | Quote

I’ll keep my fingers crossed… Thanks again!

Reply | Quote

Sorry old chap,

I could have sworn I had the code to do this. Can’t find it anywhere.

From memory you dim excel.app as new app – as you would for starting any other office app from excel – and go from there. How you define which instance new spreadsheets go to is temporarily (permanently?) beyond me.

I must admit to a small amount of surprise that no-one else has come back on this one. Hans? Legare? Moderators? Anyone?

Brooke

Reply | Quote

I haven’t jumped in because I don’t know how to make excel open a new instance from the one that is already running. I have created an application that makes Excel look like something entirely different, and I found it much harder than it sounds. In order to allow other Excel sheets to be open at the same time, you have to use event procedures (primarily workbook activate and deactivate) to setup and remove all of your changes to the Excel interface. You also have to save the initial users interface (all of his menu and tool bars, and context menues, status bars, etc.), and find all of the ways that Excel can exit to be sure you have restored things like they were. You also have to find some way to restore things if the power fails while your app is active. It took me months of trial and testing to get it right. It can be done, but it is definitely not easy.

Reply | Quote

So i’ve noticed, i’ve got quite a hairy excel beast over here myself, the result of each post i’ve put up here.

There has to be a way to make Windows 98 or Excel open a new instance if/when my database is open… Does anyone have any ideas how to do this?

Thanks!

Drk

Reply | Quote

Drk,

Try putting this code in a word document (or any controller application) and run it.

Sub Start_Mock_That_App()

Dim XLObja As Object
Set XLObja = CreateObject(“Excel.Application”)

With XLObja
.Visible = True
.Workbooks.Open FileName:= “C:Docs and SetsBrookeMy DocsMock_That_App.xls” ‘or similar
.Workbooks.add
End With

Dim XLObjb As Object
Set XLObjb = CreateObject(“Excel.Application”)

With XLObjb
.Visible = True
.Workbooks.add
End With

application.quit

End Sub

a) the user is opening a word document (put it in auto_open (?)) – even less likely to think your app has anything to do with excel(?)

you need two instances otherwise when your user opens an excel file through explorer it will try to open in the first instance.

c) this means that you don’t have to put code in the deactivate/activate events of Mock_That_App, which as Legare pointed out is a right pain to get right – and cuts down on redisplay time

d) you do still need code to change the first excel instance’s appearence in the open and close events of Mock_That_App though.

e)it may be possible to do an if…getobject(excel)….then 2apps = true before the first createobject and then put if 2apps then… around the second createobject

f) in case you’re wondering about how e) affects …….

===> if I have no excel instances open and programmatically open one instance, manually opening a spreadsheet from explorer opens it in that instance.
===> if I have no excel instances open and programmatically open two instances, manually opening a spreadsheet from explorer opens it the last (programatically) opened instance.
===> if I have one excel instance open (manually opened) and programmatically open one instance, manually opening a spreadsheet from explorer opens it in the manually opened instance.
===> if I have two excel instances open (both manually opened) and programmatically open one instance, manually opening a spreadsheet from explorer opens it in the most recently opened – manually – instance.

I have tested this on my works laptop – not thoroughly but a few times. It seems to hold true.

g)like Legare, I couldn’t get Excel to programatically start a new instance of itself. But I have had an idea. It goes like this… from excel, start word and open a document that has code in it’s auto open macro… hey! we could play this game all night!

h) the upshot? you’re slowly getting close to what you want but whether you’ll ever get there, well…

Brooke

Reply | Quote

Hi,
For what it’s worth, you can get Excel to open another instance of itself programmatically by using something like:
Dim xlApp as new excel.application
but you have to open a workbook as well to actually create that instance of Excel (I confess I have no idea why that should be!) Sadly though that doesn’t seem to help with Drk’s problem because if you then open an Excel file through explorer it opens in the original instance of Excel.
The only thing I can think of off the top of my head would be to have code in the activate and deactivate events of the MockApp workbook to hide and unhide the normal toolbars, which I think is what Legare suggested.

Reply | Quote

So the following code will work then:

Private Sub Workbook_Open()

Dim xlApp As New Excel.Application
Set xlApp = CreateObject(“Excel.Application”)

With xlApp
.Visible = True
.Workbooks.Open FileName:=”C:Docs and SetsBrookeMy Docsrevenue.xls” ‘or similar
End With

End Sub

However, I’ve got a sneaking suspicion that I got it horribly (and embarrassingly!) wrong with all that malarky about instances and which one a workbook gets opened in – it now looks to me as though they just get opened in the last instance you visited. Which means that there’s no point in opening a new instance. I shall have another look at that later on just to make sure, unless anyone else knows more about ‘instance precedents?’

Brooke

Reply | Quote

Hi Brooke,
Just for info, you don’t need the Set xlApp = CreateObject(“Excel.Application”) line in your code (Dimming it as New effectively takes care of that bit). I’m still looking into possible ways of getting Explorer to open new instances though I haven’t found anything promising as yet.

Reply | Quote

If you try Application.IgnoreRemoteRequests = True in the current instance, then clicking on a xl file in explorer will start a new instance of Excel. I don’t know what implications that might have for the proposed application.

Andrew C

Reply | Quote

Andrew,
You’re a genius! You’ve just saved me a lot of time – thanks!
Drk, I would guess that will solve your problem.

Reply | Quote

Now I am all embarrassed

Hope it does the trick

Andrew

Reply | Quote

Error:

Run-time error ‘1004’:

Method ‘IgnoreRemoteRequests of object ‘_Application’ failed

Does this method work in O97SR2?

Thanks for all your help guys!

Reply | Quote

Drk,

Sub GoAway()
Application.IgnoreRemoteRequests = True
End Sub

The above works fine in XL97 SR1.

Exit all instances of Excell and try again.
Have you any views setup ?

Andrew

Reply | Quote

Views?

I’ve placed the code in the workbook initialize event, the userform initialize event, button click initialize event, and am still getting the same error… Now i’m off to check my references, because that’s about all that’s left…

nothing out of the ordinary there…. any ideas?

Reply | Quote

Any insight on the cause of this error?

Thanks!

Reply | Quote

I’ve placed the code in the ThisWorkBook area in VBA:

***CODE START***
Private Sub Workbook_Open()
Application.IgnoreRemoteRequests = True
End Sub
****CODE END****

Thanks Andrew C! and Others!!!

Reply | Quote

The only problem I can see with that is what are you going to do if excel is already open? To my way of thinking the whole point about the seperate instance thing was so that you don’t have to worry about changing the excel interface back to normal if you want to use excel as normal whilst your app is running. I’m reasonably certain your users aren’t going to close everything down if they’ve already got excel running just so they can make your app run okay. Well, I wouldn’t – unless I had to, in which case I’d like hell about the designer of the app!

Brooke

Reply | Quote

In this environment, Excel isn’t used very often by call center reps, so that’s not an issue. The reason for the change is the userform interface of the spreadsheet. Userforms in Excel open as Modal, meaning that nothing can be done to the application until the userform is closed. The problem I was encountering is that if a rep wanted to view another excel spreadsheet, they had to close the userform manually to view the new spreadsheet.

This line wouldn’t effect any open instances of excel or any operability of the user’s machine. It essentially ‘locks down’ the instance of excel being used by the user. they can still move between windows, however if they dbl-click on another excel spreadsheet from a directory in Explorer, it will open a completely seperate instance of excel. It’s working GR8!!

Reply | Quote

User forms in Excel only open modal if you don’t tell them to open modeless.

    fmTest.Show vbModeless

Reply | Quote

Nope Legare, doesn’t like that at all.. O97SR2

Now i have a new problem..

application.ignoreremoterequests = true seems to work OK, however when I have my workbook open, and attempt to open another worksheet, I’m getting the following error:

Cannot find the file ‘C:WINDOWSDESKTOPfilename.xls’ (or one of its components). Make sure the path and filename are correct and that all required libraries are available.

I know it’s there, if I do a file..open, it’s there… what’s going on?!?

any ideas would be appreciated!

Reply | Quote

With regards to the modal thing,I thought ">jwalk had a tip about running a modeless userform for 97 but I’ve just had a (very) quick look and I can’t see anything. Maybe it’s just hidden too well for my bleary eyes.

Brooke

Reply | Quote

Jwalk did.. unfortunately it states that it can’t be done in ’97..

Any ideas on my ignoreremoterequests problem?

Reply | Quote

With reference to your post 29651, no I haven’t. But wait a minute. You’re in an isolated instance of excel because you only want one workbook open in it for presentation reasons. I’m with you so far. Then you open another workbook. Is this in the same instance or another one? The reason I ask is because I don’t understand from your post how you’re trying to open it when you get the error message. I assume it must be from code because you mention file/open seperately and double clicking from explorer would bump you into another instance. Is this assumption correct?

Brooke

Reply | Quote

Ideally, I would open my database which i’ve created.

It would assign the application.ignoreremoterequests = true to itself once it’s opened.

If I open another spreadsheet from explorer, I want a new instance to appear, I don’t want the newly opened file to open within my database. (I have a userform, we we know that Excel 97 only opens userforms as Modal, so when a spreadsheet is opened in explorer, my users are forced to close my userform manually, (the code doesn’t like this) to view the new spreadsheet)

After the spreadsheet is closed, it runs code to re-set the application.ignoreremoterequests function to False.

The problem i’m having is that while application.ignoreremoterequests = false, I am unable to open any other excel spreadsheets from explorer, I have to manually click file..open.. from within Excel to open other documents.

Clear as mud eh?

Thanks again for all your help!

Reply | Quote

The source of your problems would seem to be that you are closing excel with IRR set to true. Excel does not seem to reset IRR to false when you close it. Well, I managed to duplicate your warning message by doing just that. To check, how did you open the first instance you’ve got open? by double clicking in explorer or opening excel and then opening the file? I would check the code you’re running to make sure IRR=F is called when you close your app and also whilst you’re still developing it to have the code IRR=F attached to a macro button on a toolbar just to be on the safe side.

Brooke

Reply | Quote

On the subject of modeless forms, try stephen bullen and look for the download modelessform.zip – but take note of the warning. I haven’t tried it so can’t offer any more help.

Brooke

Edited Mar 13th 2004 to update link

Reply | Quote

Brooke,

I actually pointed Drk in that direction in another thread, but got no feedback. I suspect that it is not really useful as I could not get it to work in a meaningful way. But then perhaps I missed something.

Andrew

Reply | Quote

Sorry about that – I stumbled over it looking for more “anti-link” stuff as neither the bill manville or microsft thing are working for me. Interesting that he’s posted it if it doesn’t work. Now i’ve got two projects for the weekend!

Brooke

Reply | Quote

Brooke,

This post looks a little out of place in this thread, and I’m wondering how it relates.

I’ve posted stuff (eg, this) about using those utilities, which I’ve used OK, and I’m surprised abnout your problem.

Would you like to start a new thread if you’re having problems? I’d like to know more.

Reply | Quote

You’re right – excel links are out of place here – I was just explaining what i was doing when I came across the Stephen Bullen modeless form download (which obviusly I didn’t need to do), as a sort of semi-apology to Andrew for duplicating his post.

Reply | Quote