Solved

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

Posted on 2015-01-14
27
726 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 12
27 Comments
 
LVL 48

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 48

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 48

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 48

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 48

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 48

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
 
LVL 48

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 48

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 48

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 48

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 48

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 48

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

717 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