Word 2010 VBA: Upon opening Excel Application, need to maximize the window

Try

Set xlApp = CreateObject("Excel.application")
xlApp.WindowState = xlMaximized

Martin:

That was my first thought.  When I try that it errors out and displays the following window:

Title:  Microsoft Visual Basic for Applications
Compile error:
Variable not defined.
Buttons:  OK, Help

If you look at the break in the code, xlMaximized is highlighted.  

The Help button indicates that the problem is having the Option Explicit command at the top, which requires all variables to be declared.  

I declared xlMaximized as "variable" (didn't know how else to tag it).  When I run, I get a different error:

Title:  Microsoft Visual Basic
Run-time error '5':
Invalid Procedure call or argument
Buttons:  End, Debug

Maybe if I knew the correct way to declare the variable it would work, but perhaps it is something else.

Option Explicit is a good thing and you should always use it since it will avoid errors that are otherwise hard to find, but that's besides the point here.

In Excel, xlMaximized is a built-in constant with the value -4137, so try defining a variable of type Long. I don't do Word programming but in Excel this would work:

Const xlMaximized = -4137

If that doesn't work try


Dim xlMaximized as Long

xlMaximized = -4137

Martin:

Thank you.  I will try that as soon as I can and will report the results.

Dave

I got caught up on an unexpected deadline.  Will try this tonight.

Martin:

Thanks for chiming in!  I will try these suggestions later tonight.

Dave

Before you run it, make sure you add a reference to Microsoft Excel 14,0 Object Library (or the latest you have.

Martin:

I just tried the code from your note at 18:31:13.  I get the following error:

Title:  Microsoft Visual Basic for Applications
Compile Error:
User-defined type not defined.
Buttons:  OK, Help

....very interesting.  I decided to record a macro to see how the recorder noted the maximize window action.  Copying that line I changed your final line to the following:

xlApp.WindowState = wdWindowStateMaximize

That DOES work, in a way.  Excel opens.  It is not minimized on the taskbar, but takes up about a third of the screen.  That's not exactly what I wanted, but better than being minimized on the taskbar!

Dave

When I use Word 2010 and Excel 2010 it opens full-screen.

Interesting.  I wonder why it is different for me.  I'll tryi it on a different machine and see if it works.

I also had to set the variable declarations back to "object".

Did you add the reference? If not that is probably why you need to use objects. It may also be why you don't get full screen.

I missed that note!  I have a training this morning, but will try that as soon as I can.  Thanks.

The part where I suggested adding a reference is still good advice because it will make your code more efficient, but at this point I no longer think that it might be the cause of your problem, because I tried your code using generic objects like you did and with the addition of my xlApp.DisplayFullScreen = True line it still opened full screen.

I am setting up the same macro to work in Outlook, Word and PowerPoint.  I found a page of Excel constants.  WindowsStateMaximize can be set with the value of 0.  I tried using that value in the code.  The code works in all three systems and Excel, but still only opens to fill about a third of the screen.

I have not had time to try adding the reference, which I will try today or tomorrow.  

Thank you.  I appreciate your help with this issue!  Fortunately, my presentation got delayed until Tuesday so I have a little time to play.

Here's how you add a Reference.

Go to Tools->References

Then scroll down the list and select it. You can type "M" to get you nearer. Select it and click OK.

OK.  I finally had the chance to try this.  Yes!  Adding the reference to the Excel 14.0 Object Library did the trick.

The final line to maximize the window is this:

xlApp.WindowState = xlMaximized

HOWEVER, here is where things are still freaky.  If you run the macro from the VBE, it works beautifully; Excel opens and a new document based on the template appears maximized.  If you are in the Word editor and run the macro from the Developer ribbon, it also works beautifully.  But if you run the same macro from a button on the QAT, it shows up minimized, and appears maximized once you click on the Excel icon in the taskbar.

I am not sure why there would be a difference, but there is!

...Thought I would try using another macro to call this one, put the new macro on the toolbar and see if that worked.  No go.

Dave

If DoEvents is a valid command (no space in DoEvents) in Word VBA, put it immediately before you try the set full screen mode.

Martin:

Thanks, but that didn't help.  Do I have in the proper place:

Option Explicit

Sub OpenExcelCreateWorkSlip()

 Dim xlApp As Object, xlDoc As Object
 
 Set xlApp = CreateObject("Excel.application")
 xlApp.Visible = True
 
 Set xlDoc = xlApp.workbooks.Add("c:\users\dkong\AppData\roaming\microsoft\templates\Work_Slip_January_2015.xltx")
 DoEvents
 xlApp.WindowState = xlMaximized
 
 
End Sub

Yes, so I'm out of ideas. But I see that you are using generic objects rather than Excel objects.

Right.  I had to before I added the Excel reference.  Let me try changing that next.

The code is working with the Excel objects, but STILL doesn't maximize when I call it from the QAT!  It works fine from the Developer ribbon and the VBE.  It also doesn't work using a key code to call the macro.  

This is the final code I am using.  Any suggestions?  I am about to call it a day on this problem and move on.

Option Explicit

Sub OpenExcelCreateWorkSlip()

 Dim xlApp As Excel.Application
 Dim xlDoc As Excel.Workbook
 
 Set xlApp = CreateObject("Excel.application")
 xlApp.Visible = True
 
 Set xlDoc = xlApp.workbooks.Add("c:\users\dkong\AppData\roaming\microsoft\templates\Work_Slip_January_2015.xltx")
 DoEvents
 xlApp.WindowState = xlMaximized
 
 
End Sub

*****
Dave

As I said, I'm out of ideas, but it would be interesting to know if the little icons in the upper right-hand corner of the Excel window (like the ones shown in post ID: 40563591) indicate that the app is maximized or not.

Well, when Excel initially opens, since it is minimized you definitely don't see those window controls.  However, just clicking the item on the taskbar once immediately maximizes it.  I'll select a couple of your posts that represent the whole solution.

Dave

I would like to keep this open, as I have new information that may lead to a solution:

As we discussed above, running the Open Excel Create Work Slip macro would open Excel and start the document, but it would be in what seemed to be a minimized state on the taskbar.  However, after working with the macro, I've noticed that if the application running the macro is not maximized, Excel does indeed open and run maximized BEHIND the calling application. That has led me to believe that a code line just needs to be added to give the Excel application focus.

Dave

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.