Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open .XLSM from Web (2007)

    I have a .htm web page located on our company Intranet that has a hyper link on it. The hyperlink opens a .xlsm file that is located on the server (Many departments in the company have access to the web page and they .xlsm file. When the .xlsm file opens the workbook is Not full screen(Maximzed). If I open the same file from Windows explorer the workbook is maximized. From what I can find out there is a module in Excel called "ThisWorkbook" and if I place VBA code in this module and name the Sub "Workbook_Open()" it is supposed to run every time the workbook opens.

    I have placed the following code in the "ThisWorkbook" module

    Private Sub Workbook_Open()
    MsgBox ("Hello")
    ActiveWindow.WindowState = xlMaximized
    Application.WindowState = xlMaximized

    End Sub

    I can open the .xlsm file from windows explorer and I get the Messagebox displayed and then the application window and workbook window both go full screen. When I open the same file from the Web (We use Internet explorer and have it set to open .xlsm files in a new window) I get the Messagebox but it does not maximize the window and workbook.

    Note: I have had the Task Manager open (we are using XP) when I run this and from the Task Manager I get Excel running in the Applications tab window(It shows up with an Icon that looks more like a DOS based Icon and not the Excel Icon) and stops when I get the messagebox. When I click on the message box Excel disappears in the Task Manager and then reloads with an Excel Icon and the rest of the Workbook_Open() commands don't run. From this it appears like Excel opens twice.

    Does anyone have any ideas how I can force the Application and Workbook to open Maximized when opened from a hyperlink on an .htm page?

    Thany you.

    Lindld

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open .XLSM from Web (2007)

    Sorry about the lack of replies.

    I wouldn't bother with getting Excel to maximize. With the advent of ever-larger monitors, in particular wide-screen monitors, it is not really desirable to maximize applications. Of course, users can still do so if they want to.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    Unfortunately if you open an excel file from a hyperlink ,Excel defaults to opening the file as an embedded document inside Internet explorer. In such a case, any VBA code inside the excel file starts to behave unexpectedly (objects you expect to be there aren't for example). Running VBA in an excel file embedded in i.e. is something to avoid like the plague.

    The only way to prevent this is by setting up Windows so it opens excel files in a separate window (this will be Excel), rather than in i.e. itself, which is something you'd have to set up for each user separately (win XP):

    - open explorer and choose Tools, folder options
    - click the file types tab
    - find the xls type
    - click the advanced button and remove the check in "Browse in same window".
    - OK and repeat for other Excel file formatslike xlsm, xlsx, ...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    I agree about not worrying if it is maximized or not but it is management that has made the request so I am trying to do my best to fulfill their wish.

    Thanks for your reply,
    Lindld

  5. #5
    New Lounger
    Join Date
    May 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    We have set up our machines so when the hyperlink is opened from I.E. it does open the spreadsheet in Excel and the VBA code is still not working as expected.

    Thanks for your reply.

    Lindld

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    Move all code in the Workbook_Open event to a subroutine in a normal module and call that routine from the Workbook_Open event.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    May 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    I tried what you have stated and it failed to work as well. In my testing and reading various articles I have found the following information that may help.

    Since I am opening the Excel file from a hyperlink in an .htm page Microsoft prevents the WorkBook_Open() event from firing so I don't have any bad code that automatically runs. (This seems to be the case even though my settings tell the file to open in a new window (Excel) and not in the same window of I.E.) The controlling factor here seems to be the fact that I have started the entire process from a web page. Needless to say I have not been able to automatically run any code in the file when the file is opened from a browser window but the same file and code executes when open from Windows Explorer.

    Thanks for your help.

    Lindld

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    Does it block Auto_open as well? (in a normal module)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    New Lounger
    Join Date
    May 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    From what I can tell it blocks all VBA code that is designed to run automatically on open. I have some more testing to do but this is what I have found so far.

    Lindld

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open .XLSM from Web (2007)

    Maybe you can at least get a bit back by using other events, like workbook_activate or one of the selection change events (lots of overhead). You'll have to tell your users that it takes a switch of sheets (e.g.) to get going.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •