CODEWITHSUNDEEP
excelvbams-access
Knodel
Knodel

Reputation: 4389

Bringing an Excel window to foreground from Access

I am trying to open an Excel file from Access and it does work, however the Excel window pops up in the background (behind the Access window), which is not very user friendly. Here is the code I use: 

Private Function OpenExcelAttachment()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
With MyXL

   Dim FullPath As String, Name As String
   Name = "\ExcelFile.xlsx"
   FullPath = CurrentProject.Path & Name
   .Workbooks.Open FullPath
   .Visible = True

 End With

How can I make the Excel window appear in the foreground (on top of all opened windows) instead?

Thank you!

Upvotes: 5

Views: 15488

Answers (4)

John Osmond
John Osmond

Reputation: 63

AppActivate oWrkBk.Name & " - Excel"

Upvotes: 1

noelmcg
noelmcg

Reputation: 1067

A little late to the party here,

(using Office 2013)

If Excel isn't already open, I find that: 

.invisible = true

Brings the Excel window to the front if Excel isn't open. If Excel is already open however, I find I need to set invisible to false first then reset to true to get the window to the front

.invisible = false
.invisible = true

Maybe this should work? 

Private Function OpenExcelAttachment()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
With MyXL

   Dim FullPath As String, Name As String
   Name = "\ExcelFile.xlsx"
   FullPath = CurrentProject.Path & Name
   .Workbooks.Open FullPath

   .Visible = False
   .Visible = True

 End With

EDIT:

Actually, what seems to work far better is AppActivate

AppActivate(nameOfExcelFile)

AppActivate Statement

Upvotes: 0

QuiOui
QuiOui

Reputation: 125

You have to call AllowSetForegroundWindow before making Excel visible. I don't develop in VBA, but I think it would look like this:

Private Declare Function AllowSetForegroundWindow Lib "user32.dll" (ByVal dwProcessId As Long) As Long
Private Function OpenExcelAttachment()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
AllowSetForegroundWindow -1
With MyXL

   Dim FullPath As String, Name As String
   Name = "\ExcelFile.xlsx"
   FullPath = CurrentProject.Path & Name
   .Workbooks.Open FullPath
   .Visible = True

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

I would first check for already open instance of Excel. If you must allow for multiple instances of the Application, then it will be trickier. If you are OK with only using one instance of Excel, then I think this should work using the AppActivate statement.

Private Function OpenExcelAttachment()
Dim MyXL As Object

On Error Resume Next
Set MyXL = GetObject(,"Excel.Application")
If Err.Number <> 0 Then Set MyXL = CreateObject("Excel.Application")
On Error GoTo 0

With MyXL
   Dim FullPath As String, Name As String
   Name = "\ExcelFile.xlsx"
   FullPath = CurrentProject.Path & Name
   .Workbooks.Open FullPath
   .Visible = True
End With

AppActivate "Microsoft Excel"

End Function

Upvotes: 2

Related Questions