View Full Version : Solved: Why does this code increase the height of chart when copied as picture?

02-19-2009, 07:44 PM
Hi Everybody

Please find attached herewith a spreadsheet showing some graphs and some codes as well for copying and pasting these graphs as pictures and some code for Custom Printing facility.
This is the same code that I have posted in one of my other posts.

The company data in the spreadsheethas been removed and it has been replaced with random number data).

You will notice that the worksheet "Comparative Graphs - Orig" has 32 graphs none of which have been copied and pasted as pictures. The same 32 graphs on the "Comparative Graphs" worksheet have all been copied and pasted as pictures using code in Module1. The Custom Printing code is also available in one of the module sheets. As a result of running the code in Module1, I notice that the height of the "copied and pasted as picture" graphs is marginally greater than that of the corresponding original graphs. This can be actually can be seen on the print preview either using the built-in Print Preview or my Custom Printing code by clicking on the control on the worksheet.

To see the difference more clearly just tick the checkbox on the Sheet tab of page setup menu at the start and you will see that whilst the top of each of these picture graphs is pretty much the same but the heights have margingally increased.

Hopefully, someone might be able to point to me as to where am I going wrong? Thanks in anticipation.

Best regards

Deepak Agarwal

02-20-2009, 02:30 AM
Hello mate!

You may try to use code from my solution - thread number 25127. I have exported the pictures from excel to access and back again and the pictures, together with sheet format has been preserved.

I think this may actually help you:
xlsht.Shapes.AddPicture "C:\Data_Local\picexport.bmp", True, True, Left:=size2, Top:=size, Width:=rs!imgw, Height:=rs!imgh

Remember to keep track of each excel CELL height and each SHAPE height and width. If you cannot manage to get it working in your excel macro, then simply create another macro in which you export from ORIG sheet to HDD and then reimport to the other sheet. Basically you have to combine the functionality of my two buttons into one.

For Each sho In xlsht.Shapes
'because we have stored the number of the EXCEL row in the access table
'on our first run, now we know which row of the table needs to
' be update. So we will get the SHAPE row and launch a SELECT query to
' determine the correspondent row in the ACCESS database.
crow = sho.TopLeftCell.Row
sqlcon = "SELECT * FROM reportfc WHERE imgr=" & crow
rs.Open sqlcon, con, adOpenKeyset, adLockOptimistic
If Not SaveObjectPictureToFile(sho, "C:\Data_Local\" + sho.Name + ".bmp") Then
MsgBox "Picture was not saved!"
End If
FileToBlob "C:\Data_Local\" + sho.Name + ".bmp", rs!file, 16384
' we need rs!image to keep track of access table rows that have a
' value in the OLE OBJECT column. Otherwise we will get some weird
' errors if we do something like IF ISNULL(rs!file) then ... when
' we try to export the data back to excel and we obviously need to
' know if we have (or not) a picture in the table row.
rs!imge = 1
' we keep track of shape Height and Width (with export in mind)
rs!imgh = sho.Height
rs!imgw = sho.Width

Let me know if it worked for you! :thumb

02-21-2009, 01:29 AM

I shall try that on Monday at work and let you know how I went. Possibly I will see if can try it at home as well and let you know how I went if I could.

Best regards