View Full Version : Change format of lines from excel cells
superkj
05-07-2010, 11:53 AM
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
05-11-2010, 02:07 PM
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
05-12-2010, 01:42 AM
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
05-12-2010, 10:20 AM
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
05-12-2010, 11:17 AM
Ok that makes some sense. Still though do not select oshp it is unnecessary and will slow the loop down (a lot)
superkj
05-12-2010, 11:53 AM
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
05-13-2010, 12:52 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.