CODEWITHSUNDEEP
excelvbapowerpointshapes
Bartek
Bartek

Reputation: 93

Assign shape properties from Excel in PowerPoint

I want to assign the properties (Left, Top, Width, Height), which can be defined in the .AddShape command, by using Excel.

Now I have assigned the properties directly in the code. The goal is to modify the properties only with data (Left, Top, Width, Height) in the Excel-Workbook.

For example, I have a table in Excel with the following data, which changes the shape only by editing the data in Excel:

Length: 500    
Top: 200    
Width: 50    
Height: 20

My current code looks like this:

Sub Text_EAP()

    Dim WB As Workbook, wks As Worksheet


    Set WB = Workbooks.Open(FileName:="U:\Automatisierung\Auto.xlsx", ReadOnly:=True)   
    Set wks = WB.Worksheets("Tabelle1")   '<--- Here is the table with the property data



          Set sld = ActivePresentation.Slides(2)
Set shp = sld.Shapes.AddShape(51, 607, 195, 70, 15) '<--- The property data here shall be changed accordingly to the Excel-Data
  shp.Name = "Konzentration"
  shp.Fill.ForeColor.RGB = RGB(192, 0, 0)
  shp.TextFrame.TextRange.Text = "Konzentration"
  shp.TextFrame.TextRange.Characters.Font.Size = 6
  shp.Line.Visible = msoFalse

   'Exceldatei schliessen
    WB.Close SaveChanges:=False

End Sub

How do I have to change the code that the shape-properties will be extracted from data in the Excel-Workbook.

Thanks for your help!

Upvotes: 0

Views: 293

Answers (1)

Mikku
Mikku

Reputation: 6654

Something like this will work:

Set shp = sld.Shapes.AddShape(wks.Range("A1"), wks.Range("A2"), 195, 70, 15)

Change for other Parameters.

Code:

Sub Text_EAP()

    Dim WB As Workbook, wks As Worksheet
    Dim ex As Object

    Set ex = CreateObject("Excel.Application")

    Set WB = ex.Workbooks.Open(FileName:="U:\Automatisierung\Auto.xlsx", ReadOnly:=True)
    Set wks = WB.Worksheets("Tabelle1")

    Set sld = ActivePresentation.Slides(1)
    Set shp = sld.Shapes.AddShape(wks.Range("A1"), wks.Range("A2"), 195, 70, 15) ' Change all the values & Ranges accordingly

      shp.Name = "Konzentration"
      shp.Fill.ForeColor.RGB = RGB(192, 0, 0)
      shp.TextFrame.TextRange.Text = "Konzentration"
      shp.TextFrame.TextRange.Characters.Font.Size = 6
      shp.Line.Visible = msoFalse

    WB.Close SaveChanges:=False

End Sub

Upvotes: 1

Related Questions