3

I have a simple VBA code that copies range (some charts and tables) using CopyPicture and saves it as .gif file. Everything works perfectly when the screen is unlocked but when it's locked, Excel throws 1004 error: "cannot empty the clipboard" from time to time (about 40% of attempts).

I think I've tried everything:
- Every solution given in similar thread: CopyPicture method of range class failed - sometimes
- Doing some error handling by waiting 5s and trying to repeat (Application.Wait (Now + TimeValue("00:00:05")))
- Turning screenupdating to true Application.Screenupdating=True
- Clearing clipboard using some of solutions given in this thread: How to Clear Office Clipboard with VBA
But nothing worked.

Some sample code:

Function print(ByVal wb As Workbook, ByVal path As String, ByVal name As String)

Dim RNG as Range
Dim chtObj As ChartObject
sFile = path & name

Application.EnableEvents = False

Set RNG = Sheets(1).Range("A1:M50")
wb.Activate
Application.CutCopyMode = False
With RNG  
    .CopyPicture Appearance:=xlScreen, Format:=xlBitmap  **<-- this is where code fails**
    Set chtObj = ActiveSheet.ChartObjects.Add( _
    .Left, .top, .Width, .Height)
    chtObj.Chart.ChartArea.Format.Line.Visible = msoFalse
End With

chtObj.Chart.Paste
Application.CutCopyMode = False
chtObj.Chart.Export sFile
chtObj.Delete

Application.EnableEvents = True
End Function

I'm using Windows 10 and Wxcel 2013.

I am thinking of doing some ugly workaround by looping this function until it passes but that's the last thing I'd like to do.

TylerH
  • 20,799
  • 66
  • 75
  • 101
jabba
  • 503
  • 2
  • 6
  • 14
  • If it is possible to do this thing with a locked screen and Excel/VBA is simply being stubborn, then you may have success with a different type of ugly workaround - using Shell to copy the image with a different application. – ProfoundlyOblivious Jan 30 '20 at 21:35
  • this makes sense (the desktop window station is kind of "paused" while the lock screen is shown) and the behavior may differ between windows versions. excel cannot receive input and will likely not update its output anyway, so what is your application trying to achieve by capturing it while it is locked? – Cee McSharpface Mar 09 '20 at 16:04

1 Answers1

0

I had the same trouble and solved with a workaround: no lock of the screen using a Keyboard and Mouse blocker like this: https://sourceforge.net/projects/winkeylock/

Hemanath
  • 1,075
  • 6
  • 15
Alessandro
  • 23
  • 3