CODEWITHSUNDEEP
vbaexcelpowerpoint
Muneeb
Muneeb

Reputation: 83

VBA code to change date format of PPT slide inside text box

I am using following Excel VBA Code to insert current date in existing current power point slide. Right now,I can insert current date in Powerpoint slide in 2nd text box but I'm unable to change date format per my requirement.Date appears like 3/26/2016 while I have to modify it like March 26, 2015 Please note that I'm not taking about date insertion at footer side, I have to add date in Text box.

Sub Date()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PPshape As PowerPoint.Shape
Dim objPPTX As Object
Dim Todate As Date                  
 Todate = DateValue(Now)

Todate = Format(Todate, "mmmm d, yyyy")''tried this

'or this ?
Todate = Format(CDate(Todate), "mmmm d, yyyy")'''Tried this also

Application.DisplayAlerts = False

Set objPPTX = CreateObject("PowerPoint.Application")
objPPTX.Visible = True
objPPTX.Presentations.Open "path.pptx"

'Adding Date on First Slide

Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPApp.Visible = True
     Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex) ''copy chart on existing slide

Set PPshape = PPSlide.Shapes.AddShape(Type:=msoShapeRectangle, Left:=220, Top:=150, Width:=270, Height:=75)
With PPshape
.Fill.ForeColor.RGB = RGB(115, 111, 112)
.TextFrame.TextRange.Text = Todate
Todate = Format(Todate, "mmmm d, yyyy")
'or this ?
Todate = Format(CDate(Todate), "mmmm d, yyyy")
.TextFrame.TextRange.Font.Name = "Arial"
.TextFrame.TextRange.Font.Color = vbYellow
.TextFrame.TextRange.Font.Size = 18

End Sub

Upvotes: 1

Views: 3629

Answers (2)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

Here's your problem. You're setting the text of the text frame to Todate and THEN changing the formatting of Todate:

.TextFrame.TextRange.Text = Todate
Todate = Format(Todate, "mmmm d, yyyy")

Instead try this:

.TextFrame.TextRange.Text = Format(Now, "mmmm dd, yyyy")

Upvotes: 1

Alexander Bell
Alexander Bell

Reputation: 7918

Pertinent to the Excel VBA code mentioned in your question, the solution can be as shown in the following example:

Sub DisplayDate()
  Range("A1").Value = CDate(DateValue(Now))
  Range("A1").NumberFormat = "mmmm d, yyyy"
End Sub

Upvotes: 1

Related Questions