ImMaverick
New Member
- Joined
- Apr 5, 2016
- Messages
- 1
Little background to my question.
(Office 2010, if it helps)
I'm working on the project which should do pretty simple thing - take data from two sources and make printable pages (mean align data on the page properly, so it looks nice when printed).
The problem is - both sets of data should be properly formatted (main idea -I can't freely change column width and row height. Well, it's possible, but it's more pain in it, then possible profit), so I decided to use .Picture.Paste Link:=True to insert one set of data to avoid overformatted pages.
And my code works as intended, but really slowly. Like 15-30 seconds per page and I can have like 100 of them, so I decided to add Application.ScreenUpdating = False and now it can handle 10 or even 20 pages almost instantly. But when there are more then 20 pages error start to occur randomly.
Run-time error '1004':
Method Paste of Pictures class failed
(Or something like that - I'm translating from localized Excel) So
1) It works with screen updating (But so slow. And user shouldn't see that IMHO - that's like being naked in public)
2) If it throws error, I can manually .Copy and .Select and then script will continue for some more iterations (Like 2, or 5, or 50 - totally random)
It looks like excel unload some data when its not "in focus" from memory, so it need to be focused on it again to be able to copy it, but I can't figure how to make it with disabled screen updating.
I'm totally stuck with it, so any advise will be much appreciated.
(Office 2010, if it helps)
I'm working on the project which should do pretty simple thing - take data from two sources and make printable pages (mean align data on the page properly, so it looks nice when printed).
The problem is - both sets of data should be properly formatted (main idea -I can't freely change column width and row height. Well, it's possible, but it's more pain in it, then possible profit), so I decided to use .Picture.Paste Link:=True to insert one set of data to avoid overformatted pages.
And my code works as intended, but really slowly. Like 15-30 seconds per page and I can have like 100 of them, so I decided to add Application.ScreenUpdating = False and now it can handle 10 or even 20 pages almost instantly. But when there are more then 20 pages error start to occur randomly.
Run-time error '1004':
Method Paste of Pictures class failed
(Or something like that - I'm translating from localized Excel) So
1) It works with screen updating (But so slow. And user shouldn't see that IMHO - that's like being naked in public)
2) If it throws error, I can manually .Copy and .Select and then script will continue for some more iterations (Like 2, or 5, or 50 - totally random)
Code:
'On Error GoTo RetryHeader <-Doesn't helped
'RetryHeader:
wsTable.Activate
wsTable.Range("A5:I6").Select 'Doesn't helped either
wsTable.Range("A5:I6").Copy '<-can copy this manually (I've mentioned it above) to "bypass" error once
wsPrint.Activate
wsPrint.Range("G" & (i * 20) - 18).Select '<-can select this manually(I've mentioned it above) to "bypass" error once
wsPrint.Pictures.Paste Link:=True '<-Usually this line cause error
wsPrint.Pictures(wsPrint.Pictures.Count).Name = "picH" & i
Application.CutCopyMode = False
With wsPrint.Pictures("picH" & i)
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.PictureFormat.Crop.PictureWidth = 1165
.ShapeRange.PictureFormat.Crop.PictureOffsetX = 40
End With
'On Error GoTo RetryBody
'RetryBody:
wsTable.Activate
wsTable.Range("I" & pageBr(i) - 1).Select
wsTable.Range("A" & pageBr(i - 1) & ":I" & pageBr(i) - 1).Copy
wsPrint.Activate
wsPrint.Range("G" & (i * 20) - 17).Select
wsPrint.Pictures.Paste Link:=True '<- This line can throw same error, but rarely
wsPrint.Pictures(wsPrint.Pictures.Count).Name = "picB" & i
Application.CutCopyMode = False
With Sheets("Print").Pictures("picB" & i)
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.PictureFormat.Crop.PictureWidth = 1165
.ShapeRange.PictureFormat.Crop.PictureOffsetX = 40
End With
I'm totally stuck with it, so any advise will be much appreciated.