Application Visible with more than 1 workbook

[o0omax]

Hello,

I have a Macros that takes the workbook, saves a copy and performs copying tasks. When and how often should i CreateObject("Excel.Application") ? For every Workbook i open? how do i handle Application.Visible? Does it need to be turned True before closing the workbook?

Like the code currently operates, the opening of the workbooks is visible for a short time when excecuting the macro. After its finished and o open the created workbooks they appear grey. Does this have something to do with Application.Visible?


Option ExplicitSub PasteDiagram()
Dim PP As Object
Dim PPpres As Object
Dim xlApp As Excel.Application
Dim wbSrc As Workbook
Dim wb As Workbook
Dim k As Integer
Dim path As String
path = ActiveWorkbook.path
Set xlApp = CreateObject("Excel.Application")
Set PP = CreateObject("PowerPoint.Application")
Set wbSrc = ThisWorkbook
For k = 1 To 3
wbSrc.SaveCopyAs path & "\" & k & ".xlsm"
Set wb = Workbooks.Open(path & "\" & k & ".xlsm")
wb.Parent.DisplayAlerts = False
'wb.Parent.Visible = False
'hier ändere ich noch Daten des Workbooks
wb.Sheets("Diagramm").Range("A1") = "Änderungen wurden vorgenommen"
wb.Parent.DisplayAlerts = False
wb.Sheets("Sheet1").UsedRange.Clear
wb.Sheets("Sheet1").Visible = xlSheetVeryHidden
wb.Sheets("Sheet2").UsedRange.Clear
wb.Sheets("Sheet2").Visible = xlSheetVeryHidden
wb.Parent.DisplayAlerts = True
wb.Sheets("Diagramm").ChartObjects("Chart 2").Copy
Set PPpres = PP.Presentations.Open(path & "/Presentation1 (1).pptx", WithWindow:=False)
PPpres.Slides(1).Shapes.Paste
PPpres.SaveAs path & "\" & k & ".pptx"
wb.Close Savechanges:=True
PPpres.Save
PPpres.Close ' Savechanges:=True
Next
wbSrc.Parent.Visible = True
wbSrc.Saved = True
wbSrc.Close
PP.Quit
MsgBox "fertig"
End Sub

30039
30038

[p45cal]

When and how often should i CreateObject("Excel.Application") ? For every Workbook i open?
i don't think you should use that at all. You already have a perfectly good Excel application open.

how do i handle Application.Visible? Does it need to be turned True before closing the workbook?
I don't think you need to use it at all. If your concern is about the (brief) visibility of the created workbooks then Application.ScreenUpdating=False/True should handle that for you. It does here.

Like the code currently operates, the opening of the workbooks is visible for a short time when excecuting the macro.
Application.ScreenUpdating should handle that. See code below where screenupdating is turned off at the beginning and turned back on at the end. Be aware that if the code falls over while ScreenUpdating=False you will probably need to execute Application.ScreenUpdating=True manually in order to make the application usable (this might depend on your version of Excel (which is?)

After its finished and o open the created workbooks they appear grey. Does this have something to do with Application.Visible?
I don't know, I didn't get that here.

Your attachments have got themselves in amongst your code in the message above; I'll report the post, maybe a moderator can put that right.
Some of your lines commented-out to disable them, see also comments in the code:

Sub PasteDiagram()

Dim PP As Object
Dim PPpres As Object

Dim xlApp As Excel.Application
Dim wbSrc As Workbook
Dim wb As Workbook
Dim k As Integer
Dim path As String
path = ActiveWorkbook.path
Application.ScreenUpdating = False
'Set xlApp = CreateObject("Excel.Application")
Set PP = CreateObject("PowerPoint.Application")

Set wbSrc = ThisWorkbook

For k = 1 To 3
wbSrc.SaveCopyAs path & "\" & k & ".xlsm"


Set wb = Workbooks.Open(path & "\" & k & ".xlsm")
wb.Parent.DisplayAlerts = False
'wb.Parent.Visible = False

'hier ändere ich noch Daten des Workbooks
wb.Sheets("Diagramm").Range("A1") = "Änderungen wurden vorgenommen"

wb.Parent.DisplayAlerts = False
wb.Sheets("Sheet1").UsedRange.Clear
wb.Sheets("Sheet1").Visible = xlSheetVeryHidden

wb.Sheets("Sheet2").UsedRange.Clear
wb.Sheets("Sheet2").Visible = xlSheetVeryHidden
wb.Parent.DisplayAlerts = True

wb.Sheets("Diagramm").ChartObjects("Chart 2").Copy


Set PPpres = PP.Presentations.Open(path & "/Presentation1 (1).pptx", WithWindow:=False)

PPpres.Slides(1).Shapes.Paste
PPpres.SaveAs path & "\" & k & ".pptx"



wb.Close Savechanges:=True
PPpres.Save
PPpres.Close ' Savechanges:=True



Next

'wbSrc.Parent.Visible = True 'already visible
wbSrc.Saved = True
'wbSrc.Close 'premature/(unnecessary?) closing of this workbook (with this code in!); subsequent lines not going to be processed.
PP.Quit
Application.ScreenUpdating = True
MsgBox "fertig"
End Sub

[o0omax]

should I turn off DisplayAlerts, ScreenUpdating etc once off in the beginning and then back on at the end? Or should it be turned off/on as well in all other subs i am calling?

When I open a new workbooj, do i need to turn screenUpding off or is one time enough?

[p45cal]

should I turn off DisplayAlerts, ScreenUpdating etc once off in the beginning and then back on at the end? Or should it be turned off/on as well in all other subs i am calling?
In many versions of Excel, setting Application.ScreenUpdating to False renders the application unusable to the people using it. This is OK while a sub is running, but should be set to True when subs aren't running. So it's best always to set it to True at the end of the Sub. The only exception might be when a Sub is always called by another Sub, and never by itself; because the setting is an Application-wide setting (clue's in the code) it affects all workbooks open in that application.
Application.DisplayAlerts is an Application-wide setting too, therefore it affects all workbooks in the Excel application. If code is doing something that would normally elicit a warning (alert), such as when a person deletes a sheet, and you don't want that warning to pop-up (to allow continued uninterrupted running of the macro code) then you set it to FALSE while the code carries out that action, then normally you'd set it back to TRUE in the code so that those warnings would appear again if, say, a user deletes a sheet. Generally, this is done either side (above and below) the line which would elicit such a warning. As it happens, I don't think it's needed in the code you've shown us because I don't think there are any such actions.

When I open a new workbooj, do i need to turn screenUpding off or is one time enough?Don't do this!

[o0omax]

Thanks, i now have a better understanding of how to use those things!

I'm working on a different Macro where I am open and close many Workbooks. In the beginning of the Procedure I set Application.Visible = False. Still, when I use


Set wb = Workbooks.Open(filename)

the opened window still pops up and shows itself, even though i don*t do anything to the application. Do you know why it is?

I turned this on in the beginning and turned it off at the end (Still no performance increasement):


Public Sub SpeedUp(SpeedUpOn As Boolean) With Application
If SpeedUpOn Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayStatusBar = False 'in case you are not showing any messages


Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.DisplayStatusBar = True


End If
End With
End Sub

[snb]

Screenupdating is set to true automatically, when ending a procedure (Macro / Function).
Programming 'Application.screenupdating=true' at the end of a procedure is 100% redundant.

[p45cal]

the opened window still pops up and shows itself, even though i don*t do anything to the application. Do you know why it is?
No I don't. See the comment "Opening a new book does indeed over-ride this setting though and 'unhides' the application." here: https://stackoverflow.com/questions/54576747/vba-application-visible-false-on-workbook-open-does-not-stay-false-if-anot#54578549

Doesn't turning off screen updating hide all workbooks which you open and subsequently close in the code?



I turned this on in the beginning and turned it off at the end (Still no performance increasement):

Public Sub SpeedUp(SpeedUpOn As Boolean) With Application
If SpeedUpOn Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayStatusBar = False 'in case you are not showing any messages
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.DisplayStatusBar = True
End If
End With
End Sub
How slowly is the code running? Are we talking a second or two, or half an hour?
Screen updating will have a significant effect if there's lots of writing of data to sheets, Calculation to manual will have an effect if the code changes things which then require lots of calculations to take place, be it either long array-formulae and/or lots of them, disabling events if there are events to disable, status bar display will have little effect. So if your code doesn't cause any of these it will have little effect on performance.

[snb]

Warum so aufwändig ?


Sub M_snb()
With GetObject(ThisWorkbook.path & "\Presentation1 (1).pptx")
ThisWorkbook.Sheets("Diagramm").ChartObjects(1).Copy
.slides(1).Shapes.Paste
End With
End Sub

Schau mal: https://www.clever-excel-forum.de/Forum-Excel