Tuesday, November 24, 2020

Macro to Change the Font/Color/Size/Alignment of Text Box content PowerPoint

Macro code to ease your manual work, if you have large number of slides in your presentation and globally want to change the property of text then below code is helpful.

You can change the Font Size, Font Color, Font Property like  Bold, Italic, underline, Font Type, Shadow and many more

Use below code in Power Point file and execute below macro code

Sub TextSize()
Dim oSl As Slide
Dim oSh As Shape
With ActivePresentation
For Each oSl In .Slides
For Each oSh In oSl.Shapes
With oSh
If .HasTextFrame Then
If .TextFrame.HasText Then
.TextFrame.TextRange.Font.Size = 48
.TextFrame.TextRange.Font.Color = RGB(112, 48, 160)
.TextFrame.TextRange.Font.Italic = msoTrue
.TextFrame.TextRange.Font.Name = "Brush Script MT"
.TextFrame.TextRange.Font.Shadow = msoTrue
.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
End If
End If
End With
Next
Next
End With
End Sub

Macro Code to Copy Content from Excel File to PowerPoint, Each Row to Each Slide

Requirement
1. Need to Copy data from Excel Column A, to Power Point
2. Row 1 data Go to Slide 1
3. Row 2 data will go to Slide 2
4. Row 3 data will go to slide 3


PreRequisite
  • I have some content in Excel in Column "A"
  • Column "A", have at least 1 record in cell A1 and can have any number of records in row
  • Must have PowerPoint file with you 
  • Decide your template before hand
  • You must have slides ready in PowerPoint
  • Number of Slides must be Equal to the number of Row in Excel , which need to Copy over
  • Below Macro Code need to be execute in Excel
    1. Click on Macro
    2. Type any Name and Click on "Create" button
    3. New Popup will be there and Copy Paste Below Macro Code (Replace existing Sub/End Sub) and Save 
  • Run Code
  • On Successful execution of code, PowerPoint file will Open and Close
  • Only PowerPoint without any file stay Open, If yes Close it manually
  • On VB window, you can see the dialog box "Done"
  • If yes your code executed without any error

Sub ExcelRowtoPPT()
    Dim sht As Worksheet
    Dim PPApp As PowerPoint.Application
    Dim PPShape As PowerPoint.Shape
    Dim PPPresentation As PowerPoint.Presentation
    Dim PPTSlide As Slide
    Dim V_PPTPath As String
Dim V_PPTPath_New As String
    V_PPTPath = "C:\Users\UserName\Desktop\macro\Presentation1.pptx"
    V_PPTPath_New = "C:\Users\UserName\Desktop\macro\Presentation1.pptx"
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = msoTrue
    Set PPPresentation = PPApp.Presentations.Open(V_PPTPath)
    For Each PPTSlide In PPPresentation.Slides
        i = PPTSlide.SlideNumber
Set sht = Worksheets("Sheet1")
        sht.Activate
        Dim S2 As String
        Dim Cellname As String
        Cellname = "A" & i
            sht.Range(Cellname).Copy
            PPTSlide.Shapes.Paste
             With PPApp
                .ActiveWindow.Selection.ShapeRange.Align msoAlignTops, True
                .ActiveWindow.Selection.ShapeRange.Top = 350
                .ActiveWindow.Selection.ShapeRange.Left = 650
                .ActiveWindow.Selection.ShapeRange.Width = 400
             End With
        Application.ScreenUpdating = True
    Next
    PPApp.Activate
    PPPresentation.SaveAs V_PPTPath_New
    PPPresentation.Close
    PPApp.Quit
    Set PPShape = Nothing
    Set PPPresentation = Nothing
    Set PPApp = Nothing
    MsgBox "Done", vbOKOnly + vbInformation
End Sub


You might face clipboard Error while executing the above code, if you are still facing the error then try to re-execute the code by 2 or 3 times.

Monday, November 23, 2020

VB : run time error 429: ActiveX component can't create object

Error

run time error 429:

ActiveX component can't create object

After Having the Change from the Original Post 

To resolve error in code i used "Late Binding", then i did a attempt after few days on same work then facing a new error related to "ActiveX"

Again did some research and people/developer suggest different type of solutions but issue is straight forward.

In code you are using component of library which is not selected in reference for VBA

Solution
Note * - Your code should not in execution mode
note * - Change your code to original if you also had used "late binding"
1. Open VB
2. Go to "Tools"
3. Reference > you can see the popup "References VBAProject"
4. Scroll Down and select the required Library
**In my case I haven't selected the PowerPoint Library
so select "Microsoft PowerPoint 16.0 Object Library"
5. Go back and execute your code

VB : Compile Error: User Defined Datatype not defined

I was executing macro Code and trying to access excel and powerpoint file from the macro code. While executing the macro Code getting below error :

Compile Error: 

User Defined Datatype not defined

Here are the few lines of the Macro Code:

Dim V_Excel As Excel.Application
Dim v_book As Excel.Workbook
Dim v_sheet As Excel.Worksheet
Dim v_ppt As Presentation
Dim v_slide As Slide


Solution
After doing the R&D, getting the result from google that this problem can be solved using late binding methods

Dim V_Excel As Object
Set V_Excel = CreateObject("Excel.Application")
Dim v_book As Object
Set v_book  = CreateObject("Excel.Workbook")
Dim v_sheet As Object
Set v_sheet  = CreateObject("Excel.Worksheet")
Dim v_ppt As Object
Set v_ppt  = CreateObject("Presentation")
Dim v_slide As Object
Set v_slide = CreateObject("Slide")

Friday, November 20, 2020

ORA-12899: value too large for column ColumnName

Error  :
. . imported "DatabaseName"."TableName"                   171.3 KB    5789 rows
ORA-02374: conversion error loading table "DatabaseName"."TableName"
ORA-12899: value too large for column ColumnName (actual: 31, maximum: 30)
ORA-02372: data for row: ColumnName : 0X'5467ASH567UJGFRT4VXH567HFGHK5EWHD57GQ5'

You are trying to restore the database with IMPDP command, sometimes you will receive the error in logs, ORA-12899: value too large for column Column Name

If you faced this error too, then solve as below:

  1. Check the table name and column name from the logs, for which this error occur
  2. After restore complete alter the table and increase the size of column (using alter command)
  3. Again execute the IMPDP command for specific table only and include the below 3 parameter in your IMPDP command

Use below 3 parameter in your IMPDP command, see below parameter and description

  • remap_Table - Specify which table need to map with existing table
  • tables - Specify, which particular table need to restore
  • table_exists_action - Specify, what should be the action if table already exists, these are of 4 type  {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website


use below parameter along with the usage, see below:

  • remap_Table = OldTableName_InBackup:NewdatabasetableName
  • tables = TypeYourTableNameHere
  • table_exists_action=Truncate

See full IMPDP command here (Click Here)

web stats