Hi there,
I hope you're all well and safe
I'm a beginner at using Excel vba, but I got lost during some tasks, what I want and couldn't do is:
Is check for the first row in excel for example and see how many values I have and based on that, I want to loop through the slides and create shapes based on the number of values of the row from excel, 3 values first row then three shapes, 5 values then 5 shapes will be created or look for a slide that has 5 shapes (If a slide is already defined and has 5 shapes) and write there.
so slide 1 has 3 shapes based on 3 values, and slide 2 had 5 shapes based on 5 values and so on..( Examples in the picture)
I already can print all the values, but what I want is be able to print the shapes based on the number of values, so if I have 3 values then 3 shapes will be used, 6 values 6 shapes and like that. Let's assume that a slide has 4 shapes and I had 5 values or 3 or whatever, if it doesn't match, it will look for another slide that has the same amount of shapes as values.
Please check the pics.
My code:
I hope you're all well and safe
I'm a beginner at using Excel vba, but I got lost during some tasks, what I want and couldn't do is:
Is check for the first row in excel for example and see how many values I have and based on that, I want to loop through the slides and create shapes based on the number of values of the row from excel, 3 values first row then three shapes, 5 values then 5 shapes will be created or look for a slide that has 5 shapes (If a slide is already defined and has 5 shapes) and write there.
so slide 1 has 3 shapes based on 3 values, and slide 2 had 5 shapes based on 5 values and so on..( Examples in the picture)
I already can print all the values, but what I want is be able to print the shapes based on the number of values, so if I have 3 values then 3 shapes will be used, 6 values 6 shapes and like that. Let's assume that a slide has 4 shapes and I had 5 values or 3 or whatever, if it doesn't match, it will look for another slide that has the same amount of shapes as values.
Please check the pics.
My code:
Code:
Dim oPPTApp As PowerPoint.Application
Dim oPPTPres As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oPPTSlide As PowerPoint.Slide
Sub wrtite_service()
Dim a, b, c, d, e, f, g As String
Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = msoTrue
Set oPPTPres = oPPTApp.Presentations.Open(ThisWorkbook.Path & "\aaaa.pptx")
Count = 1
For Each cell In ActiveSheet.UsedRange
If Count > 0 Then
Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
a = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="B", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
b = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="C", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
c = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="D", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
d = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="E", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
e = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="F", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
f = ActiveCell.Offset(Count, 0).Value
Cells.Find(What:="G", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
g = ActiveCell.Offset(Count, 0).Value
Set oPPTShape = oPPTPres.Slides(Count).Shapes("a")
With oPPTShape.TextFrame.TextRange
.Text = a
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("b")
With oPPTShape.TextFrame.TextRange
.Text = b
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("c")
With oPPTShape.TextFrame.TextRange
.Text = c
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("d")
With oPPTShape.TextFrame.TextRange
.Text = d
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("e")
With oPPTShape.TextFrame.TextRange
.Text = e
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("f")
With oPPTShape.TextFrame.TextRange
.Text = f
End With
Set oPPTShape = oPPTPres.Slides(Count).Shapes("g")
With oPPTShape.TextFrame.TextRange
.Text = g
End With
Count = Count + 1
End If
Next cell
End Sub