asked on

How to Bring an Excel Sheet to the Forefront and Maximized

I created the following code to open an Excel file and select a particular sheet based on the Property Code that was previously copied into the clipboard. HOWEVER, it stay minimized and blinking on my bottom toolbar. I googled and try adding the 3 lines starting with Set MyObj=CreateObject("WScript.Shell, but that had no effect. Any suggestions to bring it to the forefront and maximized. Note, I start the whole process from a button in a Filemaker dbase layout so Filemaker is in the forefront and unfortunately remains there. Thank you.

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

xlApp.Run "'" & xlWb.Name & "'!SelectSheet"

Set MyObj=CreateObject("WScript.Shell")
myobj.appactivate "Microsoft Excel"
set myobj = nothing

'if the macro takes arguments, use:
'xlApp.Run "'" & xlWb.Name & "'!NameOfMacro", "Arg1", "Arg2", "Arg3"

'xlWb.Close True
Set xlWb = Nothing
'xlApp.Quit
Set xlApp = Nothing

Arana (G.P.)

try:
xlapp.WindowState = xlMaximized
xlwb.ActiveWindow.WindowState = xlMaximized

rvfowler2

ASKER

Obtained the error on line 13, unable to set the WindowState property of the application class. Code 800A03EC.

Norie

Does this work?

    xlapp.WindowState = -4137
    xlwb.ActiveWindow.WindowState = -4137

Open in new window

rvfowler2

ASKER

No, says "Object doesn't support his property or method: ActiveWindow". Here's the entire code I'm working with.

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

xlApp.Run "'" & xlWb.Name & "'!SelectSheet"

Set MyObj=CreateObject("WScript.Shell")
myobj.appactivate "Microsoft Excel"
set myobj = nothing

xlapp.WindowState = -4137
xlwb.ActiveWindow.WindowState = -4137

'if the macro takes arguments, use:
'xlApp.Run "'" & xlWb.Name & "'!NameOfMacro", "Arg1", "Arg2", "Arg3"

'xlWb.Close True
Set xlWb = Nothing
'xlApp.Quit
Set xlApp = Nothing

Norie

Try this.

xlApp.ActiveWindow.WindowState = -4137

Open in new window

rvfowler2

ASKER

No errors, but it still just sits minimized and blinking. Strange. Maybe need another step to maximize?

Ryan Chong

try:

xlWb.Activate
    xlWb.Application.WindowState = xlMaximized

Open in new window

rvfowler2

ASKER

Sorry, get an error that says "Unable to set the WindowState property of the Application class on line 14, which is your line xlWb.Application.WindowState = xlMaximized

Ryan Chong

Unable to set the WindowState property of the Application class on ...

we don't have a full picture what you're doing there.

is your Excel visible?

rvfowler2

ASKER

My Excel is minimized and blinking on the taskbar at the bottom. Filemaker layout is what is showing on my screen. I click on a button in Filemaker, it runs the VBS script (pasted above), which opens an Excel file and runs a macro. This macro simply chooses an Excel sheet based on the PropertyCode that has been pasted into the function. Once this is done, I simply want that Excel spreadsheet to be viewable and maximized.

Ryan Chong

is your Filemaker opened with top most? can the Filemaker be closed after launching the Excel?

rvfowler2

ASKER

Yes, but we don't want to close Filemaker; boss wants to be able to open an Excel file from FM, take a quick look, and then close it and go back to FM.

rvfowler2

ASKER

Something is wrong with the script because its not just that it doesn't work, now we're getting an error message.

Ryan Chong

I don't have Filemaker with me right now, so I can't really test this scenario. The last time I used Filemaker was about 15 years ago = /

Ryan Chong

Something is wrong with the script because its not just that it doesn't work, now we're getting an error message

what's the error message?

rvfowler2

ASKER

Sorry, get an error that says "Unable to set the WindowState property of the Application class on line 14, which is your line xlWb.Application.WindowState = xlMaximized

Entire Script:

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

xlApp.Run "'" & xlWb.Name & "'!SelectSheet"

xlWb.Activate
xlWb.Application.WindowState = xlMaximized

'if the macro takes arguments, use:
'xlApp.Run "'" & xlWb.Name & "'!NameOfMacro", "Arg1", "Arg2", "Arg3"

'xlWb.Close True
Set xlWb = Nothing
'xlApp.Quit
Set xlApp = Nothing

rvfowler2

ASKER

One other option is to not use VB to open the Excel file but to open it within FM which always brings the Excel file to the forefront, and then use the VB script to only run the macro. Tried that but failed because the VB script opens a second read-only version of the Excel file. Could we change the following file so it gives the workbook a name without actually opening it, just recognizing the file that is already open?

Set xlWb = xlApp.Workbooks.Open("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

Norie

When automating Excel from script you need to replace Excel VBA constants like xlMaximized with the value they represent, which is what I did in the code I posted earlier.

So try what Ryan suggested with a minor change.

xlWb.Activate
xlWb.Application.WindowState = -4137

Open in new window

rvfowler2

ASKER

Think we tried that one before, but added it anyway and same results. Excel blinking on taskbar. See full script below.

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open ("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

xlApp.Run "'" & xlWb.Name & "'!SelectSheet"

xlWb.Activate
xlWb.Application.WindowState = -4137

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

Sorry, out sick for 3 days. Put Application.WindowState = xlMaximized in my Select Sheet macro and it didn't work, but does it have to be in ThisWorkbook, and if so, how do you set it off? Is there a selection in Excel for auto run upon opening a workbook (like I have in Filemaker)?

rvfowler2

ASKER

Sorry all, never got around to closing this. My bad.