Resize Table Pasted From Excel,

chrismues · Jan 12th 2007

Hi,

after reading several threads and posts from Andy Pope, I managed to build a macro that modifies my table in Excel as I intended and then copies it into a defined slide of a specific ppt. So far I am happy.

On that slide is already a empty textbox. I now want to align the table's upper left corner with that of the textbox and then - with aspect ratio locked - make the table the same width as the text box.

I recorded a macro in ppt that does exactly that. I then copy/pasted the code into my Excel macro, but it doesn't work there anymore.

This is the code of the combined macro:

Code

Sub test4()


'this part copies the table in a new worksheet
'and deletes all rows that have not the value '1' in column 's'


ActiveSheet.Copy Before:=ActiveSheet
  Application.ScreenUpdating = False
  On Error Resume Next
  Dim ir As Long, mrows As Long, lastcell As Range
  Set lastcell = Cells.SpecialCells(xlLastCell)
  mrows = lastcell.Row
  'Note rows are deleted from the bottom going up
  For ir = mrows To 1 Step -1
     If Len(Trim(Range("s" & ir).Value)) = 0 Then
        Rows(ir).Delete Shift:=xlUp
     End If
  Next
  
  
  
'this part copies again the table in a new worksheet and then
'deletes all columns that have not the value '1' in row '2'
  
  ActiveSheet.Copy Before:=ActiveSheet
    Dim myrange, mycell As Range
    mycol = Sheets("Presentation outputs (3)").Range("A2", Range("IV2").End(xlToLeft)).Columns.Count
    For i = (mycol + 1) To 1 Step -1
      If Sheets("Presentation outputs (3)").Range("A2").Offset(0, i - 1) = 0 Then
      Sheets("Presentation outputs (3)").Range("A2").Offset(0, i - 1).EntireColumn.Delete
        End If
    Next i
    
    
    
'this part opens the presentation 'lalala.ppt'
'which is in the same folder as the spreadsheet
'and copies the table on slide number 2
    
    Dim objPPT As Object
    Dim objPrs As Object
     
    ActiveSheet.Range("A5:N29").CopyPicture
    Set objPPT = CreateObject("Powerpoint.application")
    objPPT.Visible = True
    objPPT.presentations.Open ThisWorkbook.Path & "\lalala.ppt"
    objPPT.presentations(1).slides(2).Select
    objPPT.ActiveWindow.ViewType = 1 'ppViewSlide
    objPPT.ActiveWindow.View.Paste
     
    
   
'the following part shall allign the top left corner of the table
'with that of a text box which is already on slide number 2,
'and then widen the table that it has the same width as the textbox
'with aspect ratio locked
      
          With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 0.65, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 0.65, msoFalse, msoScaleFromTopLeft
    End With
    With ActiveWindow.Selection.ShapeRange
        .IncrementLeft 121.5
        .IncrementTop 39#
    End With
    ActiveWindow.Selection.SlideRange.Shapes.Range(Array("Picture 8", "Rectangle 3")).Select
    ActiveWindow.Selection.ShapeRange.Align msoAlignLefts, False
    ActiveWindow.Selection.ShapeRange.Align msoAlignTops, False
    ActiveWindow.Selection.Unselect
    ActiveWindow.Selection.SlideRange.Shapes("Picture 8").Select
    With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 1.34, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.34, msoFalse, msoScaleFromTopLeft
    End With
 
      
    
    
    
    
    objPPT.presentations(1).Save
    objPPT.Quit
     
    Set objPrs = Nothing
    Set objPPT = Nothing
    
  
  Application.ScreenUpdating = True


End Sub

Display More

Can anyone help? Has ppt a different macro language than Excel? Where did I do a mistake?

Thanks.

**Andy Pope** · Jan 12th 2007

Re: Resize Table Pasted From Excel,

What exactly does "but it doesn't work there anymore" mean?
Do you get errors, if so what.
Do it move you shape but to the wrong place?

At a guess I would think the explcit shape names may cause a problem.
Whilst the textbox may remain constant the pasted picture, which is currently 'Picture 8', is likely to change.

chrismues · Jan 15th 2007

Re: Resize Table Pasted From Excel,

Andy,

'It doesn't work there anymore' means, after the table being pasted on the correct slide, nothing else happens. It gets not moved and not resized and I don't get a error message.

I post here again the code that doesn't work:

Code

'the following part shall allign the top left corner of the table
     'with that of a text box which is already on slide number 2,
     'and then widen the table that it has the same width as the textbox
     'with aspect ratio locked
     
    With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 0.65, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 0.65, msoFalse, msoScaleFromTopLeft
    End With
    With ActiveWindow.Selection.ShapeRange
        .IncrementLeft 121.5
        .IncrementTop 39#
    End With
    ActiveWindow.Selection.SlideRange.Shapes.Range(Array("Picture 8", "Rectangle 3")).Select
    ActiveWindow.Selection.ShapeRange.Align msoAlignLefts, False
    ActiveWindow.Selection.ShapeRange.Align msoAlignTops, False
    ActiveWindow.Selection.Unselect
    ActiveWindow.Selection.SlideRange.Shapes("Picture 8").Select
    With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 1.34, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.34, msoFalse, msoScaleFromTopLeft
    End With

Display More

If it was only the 'Picture 8' name that causes the problem, then the table should at least be shrunk by 0.65x, which are the first three lines of the code, and there is no 'Picture 8' mentioned yet. But even that does not happen.

chrismues · Jan 15th 2007

Re: Resize Table Pasted From Excel,

I found that someone else had a similar problem, that the name of the picture given to it by ppt changed, but it was solved using this code:

Code

ActiveWindow.Selection.SlideRange.Shapes(ActiveWindow.Selection.SlideRange.Shapes.Count).Select

I copy/pasted this code into my macro but after the table being inserted as a picture nothing more happens. I don't know where my mistake is and any more help from one of you guys would be very much appreciated. Thanks.

Chris

**Andy Pope** · Jan 15th 2007

Re: Resize Table Pasted From Excel,

You need to include the objPPT object when referencing the ActiveWindow, as you do at the begining of the code, otherwise the ActiveWindow will be Excels not PP.

chrismues · Jan 15th 2007

Re: Resize Table Pasted From Excel,

Andy pope,

you are a genius. Now it works exactly as I want it to do. Thanks very much!!!

chrismues · Jan 15th 2007

Re: Resize Table Pasted From Excel,

One more question,

The data in the table in my excel sheet is derieved from another excel file which will have different names. Currently I update the table by clicking Edit/Links/Change Source and then choose the correct file.

Can I put these steps into a macro too, so that when I run the macro it first opens the window where I choose the correct file and when I have done that and press OK all the other steps are executed?

Thanks for your efforts.

Chris

**Andy Pope** · Jan 15th 2007

Re: Resize Table Pasted From Excel,

Does the macro recorder record anything when you do those step?

chrismues · Jan 15th 2007

Re: Resize Table Pasted From Excel,

If i change the source it records the following:

Code

ChDir "T:\DEALS\M\XXX\Financial model"
    ActiveWorkbook.ChangeLink Name:= _
        "T:\DEALS\S\AAA\Financial models\Project AAA Model.xls" _
        , NewName:= _
        "T:\DEALS\M\XXX\Financial model\XXX Base Case 20061208.xls" _
        , Type:=xlExcelLinks

It autumatically updates the table with the values from the specified file. However, since the file name will always change, I would like it just to open the window where I can then choose from which file to update.

is that possible?

**Andy Pope** · Jan 16th 2007

Re: Resize Table Pasted From Excel,

Can you collect the file/folder information by using the Application.GetSaveAs method. Plenty of examples of this already on the forum.

chrismues · Jan 16th 2007

Re: Resize Table Pasted From Excel,

Thanks Andy,

will have a look at that later and then come back

Chris

chrismues · Jan 17th 2007

Re: Resize Table Pasted From Excel,

Andy,

I tried the 'Application.GetSaveAs method', but either I did not understand what you want me to do or I did not explain what I would like to achieve.

I want a macro or the code that opens the Edit/Links window. Then the user chooses the new file the workbook shall from now on be linked to, and after pressing 'OK' the links/values in the workbook are all updated and my other macro executed.

So, I have a working macro that does a specific action. But every time I open the workbook I first have to change the links via Edit/Links, and then run the macro.

I thought there might be a code that I can paste at the beginning of my macro that opens the Edit/Links dialog and after choosing, pressing ok and updating continues to execute my other macro.

I tried the GetSaveAs method but it opens the wrong dialog. Maybe something like:

Code

Sub GetEditLinks()
    Dim strFile As String
     
    strFile = Application.GetEditLinksFilename
    If strFile = "False" Then Exit Sub
     
    'and here I paste the code of my other macro
     
     
     
End Sub

Display More

But that doesn't work.

Sorry if all that sounds confusing or silly, but your help is very appreciated.

Chris

chrismues · Jan 17th 2007

Re: Resize Table Pasted From Excel,

I amended the code as follows:

Code

Dim Which_financial_model_contains_the_data As String
    ActiveWorkbook.ChangeLink Name:= _
        "T:\folder\filename.xls" _
        , NewName:= _
        "Which_financial_model_contains_the_data" _
        , Type:=xlExcelLinks

Heyyy, and now it works exactly as I want it to do.

Thanks to everybody who helped me!!

Chris

Bernardes · Oct 25th 2010

Re: Resize Table Pasted From Excel,

This is my unique solution to Paste and Centralize the chart images:

Code

Sub PasteIntoNewSld(objApp As Object)
' Author: Date: Contact: URL:
' André Bernardes 13/09/2010 09:55 [EMAIL="[email protected]"][email protected][/EMAIL] [URL]http://al-bernardes.sites.uol.com.br/[/URL]
' Cola o conteúdo em novos Slides.
' Listening: The Final Decision We All Must Take - Sagisu Shirou.
' 25.10.2010 - 15:06 - Acerta os tamanhos dos gráficos nos Slides.
 
Dim objSld As Object
 
Set objSld = objApp.ActivePresentation.Slides.Add(Index:=objApp.ActivePresentation.Slides.Count + 1, Layout:=12)
 
objApp.ActiveWindow.View.GotoSlide Index:=objSld.SlideIndex
objApp.ActiveWindow.View.Paste
 
With objApp.ActiveWindow.Selection.ShapeRange
.ScaleWidth 0.65, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.65, msoFalse, msoScaleFromTopLeft
End With
 
With objApp.ActiveWindow.Selection.ShapeRange
.IncrementLeft 121.5
.IncrementTop 39#
End With

Display More

' ScaleProportionally Method
' Align pasted chart
objApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
objApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End Sub

André Luiz Bernardes
A&A® - Work smart, not hard.
&amp;amp;quot;[email protected]&amp;amp;quot;[/email]

Blogs: Office| Excel | Access |
[Blocked Image: http://al-bernardes.sites.uol.com.br/Photos/InAnyPlace03.gif]

Rob Xaos · Oct 25th 2010

Re: Resize Table Pasted From Excel,

Bernardes,

Welcome to Ozgrid and thank you for posting an alternative solution in this thread. However in future please ensure that all VBA is wrapped in [noparse]

Code

[/noparse] tags.

Thank you.

Also I would recommend that you remove your email from the posted code. Public forums like Ozgrid are regularly harvested for email addresses by bots and you are likely to become the target to a great deal of spam if your email address remains.

Resize Table Pasted From Excel,

Participate now!

Share