Change format of lines from excel cells

[superkj]

I am new to using VBA or any programming for that matter.

I have 1 powerpoint slide that I have as a map with many lines. These lines represent a quantity of Optical fibers between two locations. If the quantity is below 10 from an excel spreadsheet I need to change the line to dashed, red. These lines have names like "stplmnos-stplmnip". I have an Excel file where I have a column with the same name type in a cell in column O and then a quantity on column K. I need to select each line in Powerpoint and find the row that matches it in excel column O and if column K > 10 then change the line to


.Presentation.Slides(1).Select
.Selection.SlideRange.Shapes("stplmnos-stplmnip").Select
.Selection.SlideRange.Shapes("stplmnos-stplmnip").Line.DashStyle = msoLineDash
.Selection.SlideRange.Shapes("stplmnos-stplmnip").Line.Visible = msoTrue
.Selection.SlideRange.Shapes("stplmnos-stplmnip").Line.ForeColor.RGB = RGB(255, 0, 0)
.Selection.SlideRange.Shapes("stplmnos-stplmnip").Line.BackColor.RGB = RGB(255, 255, 255)

[superkj]

I think I have part of the code required. Now I just have to have it look at excel.

Sub Macro1()
Dim osld As Slide
Dim oshp As Shape


For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
oshp.Select
If oshp.Name =
'scroll through excel column o' Then
if excel column k <= 10 then
oshp.Line.DashStyle = msoLineDash
oshp.Line.Visible = msoTrue
oshp.Line.ForeColor.RGB = RGB(255, 0, 0)
oshp.Line.BackColor.RGB = RGB(255, 255, 255)
End If
End If
Next
Next
End Sub

any help would be appreciated!!!

[John Wilson]

You do not need to scroll through or select the PPT shapes to modify them
if Excel condition
With ActivePresentation.Slides(1).Shapes("stplmnos-stplmnip").Line
.DashStyle = msoLineDash
.ForeColor.RGB = vbRed
End With
End If

[superkj]

Sorry, I need to be more specific. I have about 6000 lines of excel and 500 lines in powerpoint that have to be scrolled through to look and see if they match. The below code should open excel, open powerpoint, scroll through column 15 and see if the cell matches the name of a powerpoint line. If it does then do the color and dashed changes. I have it working except the part where it has to check for a powerpoint line name that matches the cell. Any help is greatly apprecitated!!!! I am having a great time learning to code.

Sub ChangePPTLineColor()

Dim PPT As PowerPoint.Application
Dim osld As Shape

'Select Powerpoint file'

ActFileName = Application.GetOpenFilename()
Set PPT = CreateObject("Powerpoint.Application")
PPT.Activate
Set PPT_file = PPT.Presentations.Open(ActFileName)

MsgBox "Select State Fiber Form"

XlsFileToOpen = Application.GetOpenFilename()

If XlsFileToOpen = False Then
Response = MsgBox("No Input file selected", vbExclamation, ctitle)
Exit Sub
End If
Workbooks.Open XlsFileToOpen

totalrows = ActiveSheet.UsedRange.Rows.Count
Count = 1
For Row = totalrows To 2 Step -1
Cells(Row, 15).Select
If Cells(Row, 15) = 'SCROLL THROUGH POWERPOINT SHAPES'
Shape.Line.DashStyle = msoLineDash
Shape.Line.Visible = msoTrue
Shape.Line.ForeColor.RGB = RGB(255, 0, 0)
Shape.Line.BackColor.RGB = RGB(255, 255, 255)

End If
Count = Count + 1

Next Row

End Sub

[John Wilson]

Ok that makes some sense. Still though do not select oshp it is unnecessary and will slow the loop down (a lot)

[superkj]

Thanks! Do you know why the following code would get stuck on the line of codeFor Each oshp In osld.Shapes when I am running it from excel? When I run it from powerpoint it works fine.

Sub Macro2()
Dim PPT As PowerPoint.Application
Dim osld As Slide
Dim oshp As Shape
ActFileName = Application.GetOpenFilename()
Set PPT = CreateObject("Powerpoint.Application")
PPT.Activate
Set PPTfile = PPT.Presentations.Open(ActFileName)


For Each osld In PPTfile.Slides
For Each oshp In osld.Shapes
If oshp.Name = "AKRNOHAH-AKRNOH25" Then
oshp.Line.DashStyle = msoLineDash
oshp.Line.Visible = msoTrue
oshp.Line.ForeColor.RGB = RGB(255, 0, 0)
oshp.Line.BackColor.RGB = RGB(255, 255, 255)
End If

Next
Next
End Sub

[John Wilson]

It needs to be declared as a PowerPoint.Shape to differentiate from an Excel shape.

Dim oshp as PowerPoint.Shape

Excel doesn't have slides so
Dim osld as Slide is Ok but I would still use PowerPoint.Slide

Also after you create the ppt Object I would set it to PPT.Visible=True