That's great. The Select is simply what is recorded using the maco recorder.
Removing it solves the problem. Thanks for the help.
I do however have another issue. If I want to resize the picture after
inserting it how can I do that without resizing it?


"Peter T" wrote:

Running your code I replicate the error.

The problem is not with the Insert but with the .Select. As you only rarely
need to select anything in vba then don't, avoid the error and speed up the
code. An example adding 5 at a time -

Sub InsertCharts2()
Dim temp1 As String
Dim i As Long, a As Long
Dim aPics(0 To 4) As Picture

temp1 = "D:\Filestore\TriAlpha\LS\DSCharts\T5983816.cg m"

range("A1").activate

Application.ScreenUpdating = False
'(140000 * 5 = 70000)

For i = 1 To 14000

For a = 0 To 4
cnt = cnt + 1
Application.StatusBar = "inserting picture number " & cnt
Set aPics(a) = ActiveSheet.Pictures.Insert(temp1)
Next

'code process 5 pictures by referring to the array

'now delete all pictures on the sheet
ActiveSheet.Pictures.Delete
Next

Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

Regards,
Peter T

"maple3" wrote in message
...
just to clarify.
i only insert 5 pictures at a time, then create the html. When the loop
repeats it deletes the 5 pictures and inserts another 5 pictures into the
same locations.

here is a simplified version of the code I am running

Sub InsertCharts()
Application.ScreenUpdating = False
For i = 1 To 70000
Application.StatusBar = "inserting picture number " & i
temp1 = "D:\Filestore\TriAlpha\LS\DSCharts\T5983816.cg m"
Range("a1").Select
ActiveSheet.Pictures.Insert(temp1).Select
Sheets("Sheet1").Shapes.SelectAll
Set sr = Selection.ShapeRange
sr.Delete
Next i
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub


if you run it in a new workbook you will find it stops on the 65536
insertion.

let me know if you agree.

maple3


"Peter T" wrote:

That's quite a bunch of pictures on a single sheet. Resources aside I
don't
think the code or the "object counter" should get stuck at 65536. I
don't
want to test to that extent with pictures but just added 70k shapes
without
error.

However if you are locating your pictures in consecutive rows you will
of
course error when you reach the bottom of the sheet.

Regards,
Peter T


"maple3" wrote in message
...
I have some VBA code which inserts pictures from files into an excel
template. The template is then used to generate an HTML file.
I run the code every day, inserting around 7500 pictures into the
template
(5 for each HTML generated)
When a picure is inserted into an excel file, regardless of what the
picture
is called, excel ascribes a number to that picture i.e. Picture1,
Picture2,
Picture3 etc in that sequence.
The problem I have is that when the Picture sequence reaches 65536,
the
VBA
code will no longer insert any more pictures. Oddly you can insert
more
pictures manually, one by one but not using a macro.

Does anyone kno how to stop this error occurring?