Application Visible with more than 1 workbook

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?

Code:

---

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

---

Attachment 30039
Attachment 30038

Quote:

---

Originally Posted by o0omax

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.

Quote:

---

Originally Posted by o0omax

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.

Quote:

---

Originally Posted by o0omax

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?)

Quote:

---

Originally Posted by o0omax

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:

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

---

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?

Quote:

---

Originally Posted by 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?

---

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.

Quote:

---

Originally Posted by o0omax

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

---

Don't do this!

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

Quote:

---

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):

Code:

---

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

---

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.

Quote:

---

Originally Posted by o0omax

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/...-anot#54578549

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

Quote:

---

Originally Posted by o0omax

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

Code:

---

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.

Warum so aufwändig ?

Code:

---

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