0

I need to execute VBA script while user is logged off Windows 10, i.e. script should be executed by schedule while lock screen. Script copy charts from every excel sheet and paste them on the last sheet.

When I executed the following code while user is logged on then script finished succesfully. When I executed the following code by task scheduller while lock screen then error occured "method copy of chartobject failed". I suppose that it's connected with impossibility to use clipboard on the windows 10 lock screen.

For Each rs In ThisWorkbook.Worksheets
   For Each ch In ActiveSheet.ChartObjects
      ch.Copy
      Sheets(Sheets.Count).Select
      Cells(i, 1).Select
      ActiveSheet.Pictures.Paste
   Next ch
   i = i + 39
Next rs
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Azat Gimadiev
  • 3
  • 1
  • 3
    I'm guessing that part of the problem is that you're using `ActiveSheet` and `Select` - [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Darren Bartrup-Cook Sep 09 '19 at 13:33
  • Also instead of `ActiveSheet.ChartObjects` you should use `rs.ChartObjects` otherwise the loop through the worksheets is pretty useless. – Pᴇʜ Sep 09 '19 at 13:45
  • Yup I use `rs.ChartObjects` instead of `ActiveSheet.ChartObjects` but nothing changed. It's not clear to me how to replace `ActiveSheet` and `Select` to Ranges as in example – Azat Gimadiev Sep 09 '19 at 13:49

1 Answers1

2

Try something like this to remove .Select and ActiveSheet statements.

For Each rs In ThisWorkbook.Worksheets
   For Each ch In rs.ChartObjects
      ch.Copy
      ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Cells(i, 1).PasteSpecial 
      i = i + 39 'should be in the inner loop (just in case)
   Next ch
Next rs
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • I know he coded it like that, but shouldn't the `i = i + 39`be inside the loop? otherwise it will paste all th shapes on the same cell. – Damian Sep 09 '19 at 13:53
  • @Damian High probability that you are right. But since he stated it runs successfully if he runs it logged in, I assumed it magically did whatever he tried to do ;) But yes maybe he should fix that. – Pᴇʜ Sep 09 '19 at 13:56
  • There is only one chart on every sheet so there is no problems with this – Azat Gimadiev Sep 09 '19 at 13:56
  • 1
    @AzatGimadiev but you are pasting every chart into the same cell on the last worksheet, is that what you intended to do? – Damian Sep 09 '19 at 13:58
  • In any case this code doesn't work and error 'object doesn't support property or method' occured again. This error happens on the line `ThisWorkbook.Sheets(Sheets.Count).Cells(i, 1).Paste` – Azat Gimadiev Sep 09 '19 at 13:59
  • replace `.Paste` with `.PasteSpecial` – Pᴇʜ Sep 09 '19 at 14:01
  • Damian, in any case the main problem is not orginize distribution of charts on the sheet but just copy and paste it on the last sheet when user is logged off – Azat Gimadiev Sep 09 '19 at 14:02
  • @Damian The `i` is no issue if there is only one `ch` chart object in every sheet, because then the `i` is incremented before the next sheet `rs`. So it works by accident. – Pᴇʜ Sep 09 '19 at 14:04
  • Рен, now error happens on `ch.Copy` line. Error is "method copy of chartobject failed" – Azat Gimadiev Sep 09 '19 at 14:17
  • Does this code run with the scheduller when you are looged in? – Pᴇʜ Sep 09 '19 at 14:23
  • and when you are logged in did you run it with the schedduler or did you start it manually? • Did you run it with the same user in the schedduler? – Pᴇʜ Sep 09 '19 at 14:38
  • Рен, your code works succesfully when I start it manually and when I start it by scheduller when I'm logged in. But error occurs when I start code by scheduller while user is logged off – Azat Gimadiev Sep 09 '19 at 14:50
  • @AzatGimadiev Did you run it with the same user in the schedduler? – Pᴇʜ Sep 09 '19 at 14:54
  • 1
    Рен, thank you so much it works in all ways!!! Checked it again, it actually works – Azat Gimadiev Sep 09 '19 at 15:02