We're exporting a set of data using .net interop to Excel and our template file contains some images.
Depending on the amount of columns we're exporting we want to position the image X pixels left of the last column depending on the image size/width. Using the record a macro function moving the Image around is a 'no op'. Setting the Shape.Left position also won't work.
How do you position an image in excel using interop X pixels left from a cell or in a fixed X/Y position on screen where X/Y are pixel locations relative to a cell.
This did not work
Dim pixels As Integer = 40
Dim cell As Excel.Range = ws.Cells(10, 10)
s.Left = s.Left - 100
After debugging for a while we noticed that this did not work on my office version. Updating my office version to 2010 made the above example work again. We added another PictureShape to replace for office 2007 fixing our own problems.
Are you sure Shape.Left won't work? If done correctly, it works fine. Try it like this (C#):
//This assumes shape is already assigned to the shape you want to move //and ws is assigned to the worksheet //set cell to whatever cell you want to move the image to Excel.Range cell = ws.Cells[10, 10]; int pixels = 40; //Number of extra pixels over from the left edge of the cell shape.Left = ((float)cell.Left + pixels); shape.Top = ((float)cell.Top);
To vb it should be something like below, but I'm not a vb expert
Dim pixels As Integer = 40 Dim cell As Excel.Range = ws.Cells(10, 10) s.Left = (CSng(cell.Left) + pixels) 'Note: if using cell.Left you must cast as single s.Top = CSng(cell.Top)
Edit: I just created a test program in VB. The following code does, indeed, move my images.
Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim oShape As Excel.Shape ' Start Excel and get Application object. oXL = CreateObject("Excel.Application") oXL.Visible = True ' Get a new workbook. oWB = oXL.Workbooks.Open("*insert_valid_path_here*") oSheet = oWB.ActiveSheet For Each oShape In oSheet.Shapes oShape.Left = oShape.Left + 9000 Next ' Make sure Excel is visible and give the user control ' of Excel's lifetime. oXL.Visible = True oXL.UserControl = True ' Make sure that you release object references. oRng = Nothing oSheet = Nothing oWB = Nothing oXL.Quit() oXL = Nothing
I suspect you are either assigning shape incorrectly or you expect it to be in the wrong spot or you are saving incorrectly.
Maybe this links will be useful:
Moving images between cells in VBA
A few Office 2013 methods changed their behavior to return ShapeRange objects opposed to just the Shape object.
You're probably getting the error because the ShapeRange object doesn't have a left property (or a top, right, et cetera). You should confirm this in the debugger.
While it's a little messy you can write something like this to keep your code compatible for both revision:
If TypeName(pic) = "ShapeRange" Then Set pic = pic(1) End If
This will pull the object your looking for in 2013 and will be ignored in 2010.