Lock down an OLE in PowerPoint

DekkaG
DekkaG used Ask the Experts™
on
I have embedded Excel worksheets in a PPT template that gets updated through vba. Depending on the criteria, sometimes the excel objects float to different location (i assume to fit better).

Is there a way to lock down the position of the table in the main template or in VBA?

Any suggestions or ideas are greatly appreciated...

Thanks...
Sub PullExcelData()

'On Error GoTo SlideError

    Set objWorksheet = objWorkbook.Worksheets("Main")
    objExcel.Calculate
    
    If objWorksheet.Range("nrHasData").Value + objWorksheet.Range("nrColCountMax").Value = 0 Then
            GoTo nodata
            
        Else: End If
    
    mbErrorSwitch = False
    Set objWorksheet = objWorkbook.Worksheets("DataTable")
    PresName = objWorksheet.Range("nrPPTLocation").Value
    strPresPath = PresName
    oPPTObjName = objWorksheet.Range("nrObjName").Value
    
    Application.DisplayAlerts = False
        
    '''''''''''''''
    
       
'    Set objWorkbook = objexcel.Workbooks.Open(ActivePresentation.Path & "\Connection Script1.2.xlsm")
    Set objWorksheet = objWorkbook.Worksheets("Main")
    Set objrange = objWorksheet.Range("nrMainCopyRange")
    
    'Instantiate the workbook object from the OLE object
    Set objExcelDestWb = objslide.Shapes(oPPTObjName).OLEFormat.Object
    
    'Define what worksheet to paste into
    Set objExcelDestSheet = objExcelDestWb.Worksheets(1)
    
    'Optionally clear destination sheet, depends what you are updating..
    objExcelDestSheet.Cells.Clear
    objWorksheet.Activate
    objrange.Copy
    
    'PASTE INTO EXCEL TABLE at cell A1
    objExcelDestSheet.Paste objExcelDestSheet.Range("A1")
        
   ' AutoFitColumns
    Dim mvgRng As Range
    
    Set objWorksheet = objWorkbook.Worksheets("Main_Formatting")
'    objExcelDestSheet.Range("A1").EntireColumn.ColumnWidth = objWoksheet.Range("nrColWidth").Value
    With objExcelDestSheet
        .Columns("A:A").AutoFit
    End With
 
    'Adjust Rows
    With objExcelDestSheet
        .Rows("1:20").AutoFit
    End With
    
    objExcel.CutCopyMode = False
    Application.DisplayAlerts = True

end sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Manually, you would do the following:
                  From link   http://office.microsoft.com/en-us/powerpoint/HA102326311033.aspx

Resize the picture to fit the slide. To do this, I right-click on my picture, click Size and Position, and then format the picture to a height of 7.5 inches (the standard slide height). Make sure to lock your picture’s Aspect Ratio or they’ll look distorted by selecting the Lock aspect ratio check box.



see this link for loads of powerpoint vba code, including code for manipulating and scaling ole objects.
                    http://skp.mvps.org/vba.htm

Author

Commented:
I am familiar with those sites, but hasn't helped me get what i need.

I have added the following lines on the top

iLeftOrig = oShp.Left
ITopOrig = oShp.Top

this seems to be picking up the correct locations.  at the bottom of the code, i have added the following:
oShp.Left = iLeftOrig
oShp.Top = ITopOrig

The problem is that if i step through the routine, it works. If I try to run it, it doesn't...

Any ideas?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I am still trying to figure this out. When I step through the powerpoint vba, the shapes align correctly.

When I try to run the whole procedure, the Shapes float to new locations. The screen isn't updating as the presentation populates the various data tabes, so I am wondering if there is something i need to do?

Do i need to set a different window property or something. I am still at a loss on why i cant get this to run.

Any help is greatly appreciated...
Excel 2007 Service Pack 2 will fix the "out of position" problem you are trying to fix.  See this link:
                      http://news.office-watch.com/t/n.aspx?a=849

Author

Commented:
To PuppyDogBuddy... I am already on SP2, but thanks for the idea.
Are you sure you fully installed SP2? Here is a look at two of the fixes:
A shape's position on a chart is not saved correctly when you save as an .xls file using Excel 2007. The shape appears in a different position when you open the file in Excel 2003.

An Excel chart, embedded as an OLE object in a PowerPoint 2007 presentation saved as a PPT file, changes size when it is subsequently activated in PowerPoint 2003.

Author

Commented:
I double checked when you sent me the first mail, because i would have been kicking myself for not being up to date. Also, i don't have 03 on my system.

Author

Commented:
I was able to figure out how to make this work. i had to insert a DoEvents to the code. Not sure why, but it is now working.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial