Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
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 "maple3" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting more than 65536 pictures into file using VBA
spot on with your guess. I meant to write 'selecting' instead of 'resizing'
Thanks for your help. Brilliant "Peter T" wrote: 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 "maple3" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you export pictures from my pictures file into a word docu | New Users to Excel | |||
Inserting Pictures | Excel Discussion (Misc queries) | |||
Inserting Pictures | Excel Discussion (Misc queries) | |||
Inserting Pictures | Excel Programming | |||
inserting pictures | Excel Programming |