inserting more than 65536 pictures into file using VBA

[Guest]

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?

 

[Peter T]

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

 

[Guest]

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.cgm"
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]

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.cgm"

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

 

[Guest]

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]

If I want to resize the picture after

inserting it how can I do that without resizing it?

That doesn't make sense !

Guessing -

Sub InsertCharts3()
Dim temp1 As String
Dim i As Long, a As Long
Dim aPics(0 To 4) As Picture
Dim lt As Single, tp As Single

temp1 = "full path to\picture.bmp"

lt = Range("C1").Left

tp = 0
For a = 0 To 4
cnt = cnt + 1
Set aPics(a) = ActiveSheet.Pictures.Insert(temp1)
With aPics(a)
.Left = lt
.Top = tp
tp = tp + .Height + 10
.TopLeftCell.Offset(0, -1).Value = .Name
End With
Next

'ActiveSheet.Pictures.Delete
End Sub

If you don't need the reference, in this case an object/Picture array, for
other purposes simply

With ActiveSheet.Pictures.Insert(temp1)
.Left = lt
'etc

You can also set position and size at same time you insert, see "AddPicture"
to Activesheet.shapes method in help. Arguably that's the proper way to do
it but with the possible disadvantage you either need to know the size in
advance.

Regards,
Peter T

 

[Guest]

spot on with your guess. I meant to write 'selecting' instead of 'resizing'
Thanks for your help.
Brilliant