Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 904
  • Last Modified:

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

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
Dave Kong
Asked:
Dave Kong
  • 14
  • 12
1 Solution
 
Martin LissRetired ProgrammerCommented:
Try

Set xlApp = CreateObject("Excel.application")
xlApp.WindowState = xlMaximized
0
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dave KongAuthor Commented:
Martin:

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

Dave
0
 
Dave KongAuthor Commented:
I got caught up on an unexpected deadline.  Will try this tonight.
0
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
Martin:

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

Dave
0
 
Martin LissRetired ProgrammerCommented:
Before you run it, make sure you add a reference to Microsoft Excel 14,0 Object Library (or the latest you have.
0
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
When I use Word 2010 and Excel 2010 it opens full-screen.
...
0
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
I missed that note!  I have a training this morning, but will try that as soon as I can.  Thanks.
0
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
Yes, so I'm out of ideas. But I see that you are using generic objects rather than Excel objects.
0
 
Dave KongAuthor Commented:
Right.  I had to before I added the Excel reference.  Let me try changing that next.
0
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Dave KongAuthor Commented:
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
 
Dave KongAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now