Display opened workbook on screen

[mikjp]

Excel 2010 & 2016: I would like to open a selected workbook and see it on screen to be worked with. At present it only loads in the taskbar. Is there way to code this or do I use Sendkeys to make it 'active'?

 

[Joe4]

assigned a keyboard shortcut, and it opens directly.

Ah, that's the key right there. You are calling/running the code manually, from a keyboard shortcut.

Since your name was so close to the "Workbook_Open" event procedure (which is VBA code that runs automatically when that particular file opens), I wasn't sure if that was what you are intending, and just made a typo. It does not appear to be the case.

Sorry for the confusion.

 

[mikjp]

Well, jon, I'm stumped. If I run this macro in "Book4.xlsm" in 2016 from the IDE on my machine, it ONLY opens "Book5.xlsx" on the taskbar.

Sub Open_Workbook()
Dim destWB As Workbook
    Workbooks.Open "Z:\Book5.xlsx"
    Set destWB = ActiveWorkbook
End Sub

Is it perhaps because you use an assigned key and I run it from the IDE?
Anyone?

 

[mikjp]

Bizarre! If I re-name the sub to Autpen it runs 'normally' when I open the host workbook. HOWEVER, if I run that sub in the IDE it does not.

 

[mikjp]

OK - you can all relax. Thanks to Stack Overflow I have the solution and 'cos I'm a decent guy I'll post it here for you all.

A new instance of Excel is required.

    Dim xls As Excel.Application
    Dim wb0 As Excel.Workbook
    Set xls = New Excel.Application
    Set wb0 = xls.Workbooks.Open("blah blah", ReadOnly:=False)
    xls.Visible = True

Bingo!

Thanks, Stack!
Only two hurdles left to jump now
1) It opens 2 copies of the workbook ????
2) It opens not 'maximised'.

I'll get there.

 

[jondavis1987]

Try throwing this in there. I don't know why it would open two copies.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]WindowState [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlMaximized [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'maximize Excel[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    ActiveWindow[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]WindowState [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlMaximized [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'maximize the workbook in Excel[/FONT][/COLOR]</code>