Solved

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

Posted on 2015-01-14
27
495 Views
Last Modified: 2016-02-10
I have created the following code for MS Word VBA to open Excel and create a new file based on a template.  The macro is working fine, but I need to maximize the Excel window.  Can someone tell me how to do that?

Sub TestOpenExcel()

 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")

End Sub
0
Comment
Question by:Dave Kong
  • 14
  • 12
27 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40550450
Try

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

Author Comment

by:Dave Kong
ID: 40550473
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40550480
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
0
 

Author Comment

by:Dave Kong
ID: 40556495
Martin:

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

Dave
0
 

Author Comment

by:Dave Kong
ID: 40563487
I got caught up on an unexpected deadline.  Will try this tonight.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40563539
As my first foray into Word VBA, I found that this works.

Dim xlApp As Excel.Application, xlDoc As Excel.Workbook
 
Set xlApp = CreateObject("Excel.application")
xlApp.Visible = True
 
Set xlDoc = xlApp.workbooks.Add("c:\solutions\Q_28597315.xlsm")
xlApp.DisplayFullScreen = True

Open in new window

0
 

Author Comment

by:Dave Kong
ID: 40563548
Martin:

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

Dave
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40563568
Before you run it, make sure you add a reference to Microsoft Excel 14,0 Object Library (or the latest you have.
0
 

Author Comment

by:Dave Kong
ID: 40563581
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40563591
When I use Word 2010 and Excel 2010 it opens full-screen.
...
0
 

Author Comment

by:Dave Kong
ID: 40563661
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".
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40563699
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.
0
 

Author Comment

by:Dave Kong
ID: 40566760
I missed that note!  I have a training this morning, but will try that as soon as I can.  Thanks.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 40566778
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.
0
 

Author Comment

by:Dave Kong
ID: 40568313
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40568395
Here's how you add a Reference.

Go to Tools->References
Tools->References
Then scroll down the list and select it. You can type "M" to get you nearer. Select it and click OK.
Select it
0
 

Author Comment

by:Dave Kong
ID: 40571762
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40571782
If DoEvents is a valid command (no space in DoEvents) in Word VBA, put it immediately before you try the set full screen mode.
0
 

Author Comment

by:Dave Kong
ID: 40571786
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40571797
Yes, so I'm out of ideas. But I see that you are using generic objects rather than Excel objects.
0
 

Author Comment

by:Dave Kong
ID: 40571814
Right.  I had to before I added the Excel reference.  Let me try changing that next.
0
 

Author Comment

by:Dave Kong
ID: 40574312
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40574330
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.
0
 

Author Comment

by:Dave Kong
ID: 40574354
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
0
 

Author Comment

by:Dave Kong
ID: 40627318
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40627771
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now