Advertisement
Guest User

Untitled

a guest
Apr 18th, 2014
56
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.49 KB | None | 0 0
  1. str = ""
  2. str = "Release Date" & str & objWorksheet.Cells(i, 1).Value & Chr(13)
  3. str = "Distributor" & str & objWorksheet.Cells(i, 2).Value & Chr(13)
  4. str = "Genre" & str & objWorksheet.Cells(i, 10).Value & Chr(13)
  5. str = "Starring" & str & objWorksheet.Cells(i, 7).Value & Chr(13) & Chr(13)
  6. str = str & objWorksheet.Cells(i, 14).Value
  7.  
  8. Sub CreateSlides()
  9. 'Dim the Excel objects
  10. Dim objWorkbook As New Excel.Workbook
  11. Dim objWorksheet As Excel.Worksheet
  12.  
  13. 'Dim the File Path String
  14. Dim strFilePath As String
  15.  
  16. 'Dim the PowerPoint objects
  17. Dim PPT As Object
  18. Dim pptSlide As PowerPoint.Slide
  19. Dim pptLayout As PowerPoint.CustomLayout
  20. Dim pptNewSlide As PowerPoint.Slide
  21. Dim str As String
  22. Dim Title As String
  23.  
  24. Set PPT = GetObject(, "PowerPoint.Application")
  25.  
  26. PPT.Visible = True
  27.  
  28. 'Get the layout of the first slide and set a CustomLayout object
  29. Set pptLayout = PPT.ActivePresentation.Slides(1).CustomLayout
  30.  
  31. 'Run the OpenFile function to get an Open File dialog box. It returns a String containing the file and path.
  32. strFilePath = OpenFile()
  33.  
  34. 'Open the Excel file
  35. Set objWorkbook = Excel.Application.Workbooks.Open(strFilePath)
  36.  
  37. 'Grab the first Worksheet in the Workbook
  38. Set objWorksheet = objWorkbook.Worksheets(1)
  39.  
  40. 'Loop through each used row in Column A
  41. For i = 2 To objWorksheet.Range("A65536").End(xlUp).Row
  42.  
  43. Set PPT = GetObject(, "PowerPoint.Application")
  44.  
  45. Set pptNewSlide = PPT.ActivePresentation.Slides.AddSlide(PPT.ActivePresentation.Slides.Count + 1, pptLayout)
  46.  
  47. 'Get the number of columns in use on the current row
  48. Dim LastCol As Long
  49. Dim boldWords As String
  50.  
  51.  
  52. boldWords = "Release Date: ,Distributor: ,Genre: ,Starring: "
  53. LastCol = objWorksheet.Rows(i).End(xlToRight).Column
  54. If LastCol = 16384 Then LastCol = 1 'For some reason if only column 1 has data it returns 16384, so correct it
  55.  
  56. 'Build a string of all the columns on the row
  57. str = ""
  58. str = "Release Date: " & str & objWorksheet.Cells(i, 1).Value & Chr(13) & _
  59. "Distributor: " & objWorksheet.Cells(i, 2).Value & Chr(13) & _
  60. "Genre: " & objWorksheet.Cells(i, 10).Value & Chr(13) & _
  61. "Starring: " & objWorksheet.Cells(i, 7).Value & Chr(13) & Chr(13) & _
  62. objWorksheet.Cells(i, 14).Value
  63.  
  64. sfile = Cells(i, 3) & ".jpg"
  65.  
  66. Set PPT = GetObject(, "PowerPoint.Application")
  67.  
  68. spath = "FILEPATHGOESHERE"
  69.  
  70.  
  71. 'Write the string to the slide
  72. pptNewSlide.Shapes(2).TextFrame.TextRange.Text = objWorksheet.Cells(i, 3).Value 'This enters the film Title
  73. PPT.ActivePresentation.Slides(PPT.ActivePresentation.Slides.Count).Shapes(1).TextFrame.TextRange.Text = str
  74.  
  75. BoldSomeWords PPT.ActivePresentation.Slides(PPT.ActivePresentation.Slides.Count).Shapes(1), str, boldWords
  76.  
  77. Sub Main()
  78. 'This will be the calling procedure, so it can be the one which
  79. ' builds the concatenated string, but also will be where you define
  80. ' which parts of this string should be bold
  81. Dim str$
  82. Dim boldWords as String
  83.  
  84. 'Establish a string of words/phrases to bold, to be used later
  85. boldWords = "Release Date,Distributor,Genre,Starring"
  86.  
  87. 'Create your concatenated string
  88. ' I modified this because I don't think it was correct
  89. str = ""
  90. str = "Release Date" & str & objWorksheet.Cells(i, 1).Value & Chr(13) & _
  91. "Distributor" & objWorksheet.Cells(i, 2).Value & Chr(13) & _
  92. "Genre" & objWorksheet.Cells(i, 10).Value & Chr(13) & _
  93. "Starring" & objWorksheet.Cells(i, 7).Value & Chr(13) & Chr(13) & _
  94. objWorksheet.Cells(i, 14).Value
  95.  
  96. 'Put these words in a shape:
  97. ActivePresentation.Slides(1).Placeholders(1).TextFrame.TextRange.Text = str
  98.  
  99. 'Send your string, the list of bold words, and the shape in to a subroutine
  100. ' that will apply the bold font
  101. BoldSomeWords ActivePresentation.Slides(1).Shapes(1), str, boldWords
  102.  
  103. Sub BoldSomeWords(shp As Object, str As String, myWords As String)
  104.  
  105. Dim word As Variant
  106. Dim iStart As Integer, iEnd As Integer
  107.  
  108. 'Convert the list of words in to an iterable array, and
  109. ' iterate it.
  110. For Each word In Split(myWords, ",")
  111. 'Loop just in case there are duplicates, or omit the Do Loop if
  112. ' that is not a concern.
  113. Do Until InStr(iEnd + 1, str, word) = 0
  114. iStart = InStr(iStart + 1, str, word)
  115. iEnd = iStart + Len(word)
  116. shp.TextFrame.TextRange.Characters(iStart, Len(word)).Characters.Font.Bold = msoTrue
  117. Loop
  118. Next
  119.  
  120. End Sub
  121.  
  122. BoldSomeWords(shp As Object, str As String, myWords As String)
  123.  
  124. BoldSomeWords(shp As PowerPoint.Shape, str As String, myWords As String)
  125.  
  126. pptNewSlide.Shapes(1).TextFrame.TextRange.Text = str
  127.  
  128. BoldSomeWords pptNewSlide.Shapes(1), str, boldWords
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement