0

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!

Erik A
  • 31,639
  • 12
  • 42
  • 67
dpberry178
  • 558
  • 6
  • 21
  • 2
    Are you sure that you are referring to the correct application? `AppExcel.Visible = False` should work, it may be that you are openning 2 Excel applications somehow and trying to set `.Visible = False` to only one of them. Btw, the default property of `.Visible` is false. – Vityata Feb 26 '18 at 16:14
  • Try `AppExcel.Application.Visible = False` – Ryan Wildry Feb 26 '18 at 16:17
  • @RyanWildry: I replaced AppExcel.Visible = False with AppExcel.Application.Visible = False with the same result; Excel still opens visibly. – dpberry178 Feb 26 '18 at 16:35
  • @Vityata: Hmmm, the only code that is dealing with opening the Excel app is the code I posted here, so I am not sure how multiple sessions would be opened? – dpberry178 Feb 26 '18 at 16:36
  • @dpberry178 - no idea as well. But what happens if you search for `.Visible` in the whole project? Does it find anything more? – Vityata Feb 26 '18 at 16:38
  • @Vityata: No other occurrence of ".Visible" is found when searching the project. – dpberry178 Feb 26 '18 at 16:42
  • I see. What happens if you restart your PC? (this is no joke, honestly), @dpberry178 ? – Vityata Feb 26 '18 at 16:44
  • @Vityata: That is an interesting suggestion; this issue has continued despite several restarts since it first appeared, however, I am not using a traditional PC; it is just a terminal that connects to a virtual Windows desktop, so I am not really sure whether a restart has the same effect... – dpberry178 Feb 26 '18 at 16:49
  • @dpberry178 - can you restart the virtual Windows desktop? – Vityata Feb 26 '18 at 16:50
  • @Vityata: I have restarted the virtual desktop and I am still seeing Excel open visibly. :( – dpberry178 Feb 26 '18 at 16:56
  • @dpberry178 - and if you go step-by-step with `F8`, Excel opens exactly after `Set AppExcel = CreateObject("Excel.Application")` and becomes visible immediately? – Vityata Feb 26 '18 at 16:57

1 Answers1

1

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thanks for your continued help; I took your advice and stepped through the code line by line. The Excel application is indeed opening invisibly. It is becoming visible later when I actually refresh the charts in the PowerPoint file: shp.Chart.ChartData.Activate shp.Chart.Refresh. Any idea on how to prevent that? – dpberry178 Feb 26 '18 at 18:13
  • @dpberry178 - just guessing that if you use `Application.EnableEvents = False` & `Application.ScreenUpdating = False` for the Excel app you might get lucky with the not visibility of the Excel. – Vityata 6 mins ago – Vityata Feb 27 '18 at 09:34