CODEWITHSUNDEEP
ms-accessvbaexcel.application
dpberry178
dpberry178

Reputation: 616

How do I open an invisible Excel session from Access via VBA?

I am sorry to pose this seemingly simple question, but I have read many proposed solutions on how to open an invisible Excel window via VBA, but none of them have been specifically about doing it from Access, nor have any of the proposed solutions worked for my particular situation.

I am trying to open an Excel session from Access in order to refresh some data that is referenced by charts in another (PowerPoint) document.

This is the code I am using:

'refresh the excel tables that drive the charts
DoCmd.OpenQuery ("qryCreateMemberCountByType")
DoCmd.OpenQuery ("qryCreatePaymentsByType")
Dim AppExcel As Object
Dim wb As Object
Set AppExcel = CreateObject("Excel.Application")
AppExcel.Visible = False 'this doesn't work
AppExcel.DisplayAlerts = False
With AppExcel.Workbooks.Open("C:\MyPath\Template.xlsx")
    '.Windows(1).Visible = False 'this doesn't work
    .RefreshAll
    .Save
    .Close
End With
AppExcel.Quit
Set AppExcel = Nothing

Among the multiple methods I have tried are:

AppExcel.Visible = False

and

.Windows(1).Visible = False

Nothing seems to prevent Excel from opening a visible window (and leaving it open until manually closed, despite my attempts to close it via code).

Note - for PowerPoint, I was able to do it quite elegantly:

With AppPPT.Presentations.Open("C:\MyPath\Template.pptx", WithWindow:=False)

UPDATE:

I discovered that the Excel application is actually opening invisibly; it is becoming visible later, when I refresh the charts in PowerPoint that reference the tables in Excel:

For Each Slide In .Slides
    For Each shp In Slide.Shapes
        'refresh charts
        If shp.HasChart Then
           shp.Chart.ChartData.Activate 'this line results in a visible Excel session
           shp.Chart.Refresh 'this line also results in a visible Excel session
        End If
    Next shp
Next Slide

Any ideas on how to prevent Excel from becoming visible when refreshing the charts would be great!

Upvotes: 0

Views: 1009

Answers (1)

Vityata
Vityata

Reputation: 43585

The whole case seems rather interesting. Just to make sure that we are on the same step, can you make your code as small as this one:

Public Sub TestMe()
    Dim appExcel As Object
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = False 'this is a bit useless, as it is the default, but still
End Sub

and check whether it works as expected? Then think about adding additional lines to make it mimic your original code.

Upvotes: 1

Related Questions