Excel not showing in Windows 7 but does in XP

[Pat]

I have a Access application that creates an xls file then tries to open the xls file for the user to play with.
This shows fine in Windows XP but does not show in Windows 7.
Are the parameters the same for both OS's?
The command is:

Code: Select all

    ShellExecute Application.hWndAccessApp, "Open", sPathFn, 0&, 0&, SW_SHOWMAXIMIZED

A module containing the following:

Code: Select all

Public Declare Function ShellExecute _
    Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMAXIMIZED = 3

Does any thing in the Declare need to be changed for Windows 7?

Any ideas?

[Pat]

It's not windows at all, it works on an MDB file but not on the MDE file.
What could be the problem?

[HansV]

What happens if you compile the code in the .mdb file? Select Debug | Compile <projectname> in the Visual Basic Editor. Do you get an error message?

[Pat]

No i don't get an error, in fact i couldn't create an mde file unless the code compiled correctly in the mdb file, right?

[HansV]

Probably, but I'm not 100% certain.

Try this temporarily:

MsgBox ShellExecute(Application.hWndAccessApp, "Open", sPathFn, 0&, 0&, SW_SHOWMAXIMIZED)

Perhaps the value returned by ShellExecute will provide a clue.

[Pat]

The value returned is 33.

[HansV]

Thanks. That is both good news and bad news.
The good news is that a return value greater than 32 means that ShellExecute completed successfully.
The bad news is that we still don't have a clue why you don't get the expected result.

Have you checked whether the Excel workbook is opened behind Access?

And what happens if you insert a line

AppActivate "Microsoft Excel"

after the line that calls ShellExecute?

[Pat]

I will get the client to try that tomorrow.
Remember it works in the MDB file but not the MDE file.
What does that line do?

[HansV]

AppActivate tries to bring the specified application to the front.

[Pat]

I asked them to runtask manager and see if excel was amongst the apps running and it wasn't.
Seems there is something in an MDE file which doesn't quite work.
Here is the code that creates the Excel file and inserts a heading into it.

Code: Select all

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQ, sPathFn, True
    ''''DoCmd.TransferSpreadsheet acExport, , sQ, sPathFn, True
    Dim objapp As Object
    Dim objwb As Object
    Dim objsh As Object
    
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    
    Set objwb = objapp.Workbooks.Open(sPathFn)
    Set objsh = objwb.Sheets(1)
    
    objsh.Rows(1).Insert
    
    objsh.Cells(1, 1) = sH
    objwb.Close SaveChanges:=True
    
    objapp.Quit
    
    Set objsh = Nothing
    Set objwb = Nothing
    Set objapp = Nothing
    
    ''''Application.FollowHyperlink sPathFn

    ShellExecute Application.hWndAccessApp, "Open", sPathFn, 0&, 0&, SW_SHOWMAXIMIZED
    '''Shell ("excel.exe " & Chr(34) & sPathFn & Chr(34))
    
    '''DoCmd.Close acForm, Me.Name, acSaveNo
Exit_CommandExportExcel_Click:
    Exit Sub
Err_CommandExportExcel_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CommandExportExcel_Click

I will put a msgbox just prior to the ShellExecute command, maybe it's a timing thing.
I will put a msgbox after it as well that shows the err number.

Any other ideas?

[JohnH]

Is there a reason you don't just the FollowHyperlink method you have commented out?

Or with the earlier objapp just make it visible and active rather than closing it?

[Pat]

And what happens if you insert a line

AppActivate "Microsoft Excel"

after the line that calls ShellExecute?

That results in an error.
:
5 - Invalid procedure call or argument

[Pat]

Is there a reason you don't just the FollowHyperlink method you have commented out?

Or with the earlier objapp just make it visible and active rather than closing it?

The followhyperlink does not work.

If i leave the objapp open can they edit the xls file? If so, when do i close the objapp?

[Pat]

I have changed the code to:

Code: Select all

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQ, sPathFn, True
    ''''DoCmd.TransferSpreadsheet acExport, , sQ, sPathFn, True
    Dim objapp As Object
    Dim objwb As Object
    Dim objsh As Object
    
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    
    Set objwb = objapp.Workbooks.Open(sPathFn)
    Set objsh = objwb.Sheets(1)
    
    objsh.Rows(1).Insert
    
    objsh.Cells(1, 1) = sH
    objwb.Close SaveChanges:=True
    Set objwb = objapp.Workbooks.Open(sPathFn)          '<<<<< new
    
'    objapp.Quit                                                             '<<< commented
    
    Set objsh = Nothing
    Set objwb = Nothing
    Set objapp = Nothing
''MsgBox "Prior to ShellExecute of Excel"
''Application.FollowHyperlink sPathFn
    '??????????ShellExecute Application.hWndAccessApp, "Open", Chr(34) & sPathFn & Chr(34), 0&, 0&, SW_SHOWMAXIMIZED
''AppActivate "Microsoft Excel"
    '''Shell ("excel.exe " & Chr(34) & sPathFn & Chr(34))
    '''DoCmd.Close acForm, Me.Name, acSaveNo

Do I need to execute an objapp.quit (clean up reasons), if so where would i put it?

[HansV]

In this version, you should not quit Excel.

Change

Code: Select all

    objwb.Close SaveChanges:=True
    Set objwb = objapp.Workbooks.Open(sPathFn)          '<<<<< new
    
'    objapp.Quit                                                             '<<< commented

to

Code: Select all

    objwb.Save
    objapp.Visible = True

[Pat]

Thanks Hans, i will get them to try that tomorrow

[Mark L]

No i don't get an error, in fact i couldn't create an mde file unless the code compiled correctly in the mdb file, right?

That is true, you can't get an mde if it has compile errors.

What version of Access are you running?

[Pat]

2007

[Mark L]

I've had situations with some clients where Excel opens (or maybe it was Word), but does not pop to the front. You could see it on the Windows Taskbar, you just had to click it to bring it to the front. I don't know why it happens; not sure if it is an Office or Windows problem.

[Pat]

The strange thing about this is that the mdb works fine, it's the mde that has the problem.
The client says that excel is not even on the task bar.