RAMAKAYAL COMPUTER EDUCATION

Author: Saravana Kumar

  • Mail Merge in Powerpoint – Single Image Each Slide automatically

    Sub MailMergeWithImages()
    Dim ppt As Presentation
    Dim slide As slide
    Dim excelApp As Object
    Dim wb As Object
    Dim ws As Object
    Dim i As Integer
    Dim nameText As String
    Dim imagePath As String
    Dim shape As shape
    Dim imgShape As shape
    Dim originalSlide As slide
    Dim newSlide As slide
    Dim imagePlaceholder As shape

    
    Set excelApp = CreateObject("Excel.Application")
    Set wb = excelApp.Workbooks.Open("D:\AUTOMATION\ExcelFile.xlsx") 
    Set ws = wb.Sheets(1)

   
    Set ppt = ActivePresentation

    If ppt.Slides.Count > 0 Then
        Set originalSlide = ppt.Slides(1) 

   
        i = 2
        Do While ws.Cells(i, 1).Value <> ""
       
            nameText = ws.Cells(i, 1).Value
            imagePath = ws.Cells(i, 2).Value
            
         
            Set newSlide = ppt.Slides.Add(ppt.Slides.Count + 1, ppLayoutText)
            
           
            For Each shape In originalSlide.Shapes
                shape.Copy
                newSlide.Shapes.Paste
            Next shape
            
          
            For Each shape In newSlide.Shapes
                If shape.HasTextFrame Then
                    If shape.TextFrame.TextRange.Text Like "*[Title]*" Then
                        shape.TextFrame.TextRange.Text = Replace(shape.TextFrame.TextRange.Text, "[Title]", nameText)
                    End If
                End If
            Next shape
            
           
            For Each shape In newSlide.Shapes
                If shape.Name = "MyImages" Then 
                    Set imagePlaceholder = shape
                    Exit For
                End If
            Next shape
            
         
            If Dir(imagePath) <> "" Then
                
                If Not imagePlaceholder Is Nothing Then
                    Set imgShape = newSlide.Shapes.AddPicture(imagePath, _
                        MsoTriState.msoFalse, MsoTriState.msoCTrue, _
                        imagePlaceholder.Left, imagePlaceholder.Top, _
                        imagePlaceholder.Width, imagePlaceholder.Height)
                Else
                    MsgBox "Image placeholder not found.", vbExclamation, "Error"
                End If
            Else
                MsgBox "Image not found: " & imagePath, vbExclamation, "Error"
            End If
            
     
            i = i + 1
        Loop
    Else
        MsgBox "No slide to copy from. Please ensure the presentation has at least one slide.", vbCritical, "Error"
    End If

   
    wb.Close False
    excelApp.Quit
    Set excelApp = Nothing
End Sub

  • To Delete Blank Rows in excel using VBA

    Here’s a step-by-step guide to delete blank rows using VBA:

    Step 1: Open Excel and Enable the Developer Tab

    1. Open Excel and load your workbook.
    2. Enable the Developer tab if it’s not visible:
      • Go to File > Options.
      • In the Excel Options dialog box, click Customize Ribbon.
      • On the right side, check Developer and press OK.

    Step 2: Open the VBA Editor

    1. Click on the Developer tab.
    2. Click Visual Basic or press Alt + F11 to open the VBA editor.

    Step 3: Insert a New Module

    1. In the VBA editor, click on Insert in the top menu.
    2. Select Module to add a new module.

    Step 4: Write the VBA Code to Delete Blank Rows

    Copy and paste the following VBA code into the module:

    Sub DeleteBlankRows()
        Dim i As Long
        Dim lastRow As Long

        ' Find the last row with data in column A
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        
        ' Loop from the last row to the first row
        For i = lastRow To 1 Step -1
            ' Check if the entire row is blank
            If WorksheetFunction.CountA(Rows(i)) = 0 Then
                Rows(i).Delete
            End If
        Next i
    End Sub

    Code Explanation:

    • lastRow = Cells(Rows.Count, 1).End(xlUp).Row: This line finds the last row with data in column A.
    • For i = lastRow To 1 Step -1: Loops through each row from the last row to the first row.
    • If WorksheetFunction.CountA(Rows(i)) = 0: Checks if the entire row is empty (no values).
    • Rows(i).Delete: Deletes the row if it is blank.

    Step 5: Run the Macro

    1. After pasting the code, close the VBA editor or minimize it.
    2. Go back to the Excel workbook.
    3. Press Alt + F8 to open the Macro dialog.
    4. Select DeleteBlankRows from the list and click Run.

    Step 6: Verify the Results

    • The macro will go through each row in your sheet, starting from the last row, and delete any rows that are completely blank.

    Notes:

    • The code deletes rows that are entirely blank. If a row has data in even one cell, it won’t be deleted.
    • If you want to target a specific column for blank rows instead of the entire row, you can modify the If condition to check only that column, like this
    If Cells(i, 1).Value = "" Then
    Rows(i).Delete
End If
    • This will delete rows where column A is empty.

  • To combine data from multiple sheets into one sheet using a VBA macro

    Step 1: Open the VBA Editor

    1. Open your Excel workbook.
    2. Go to the Developer tab and click on Visual Basic (or press Alt + F11).

    Step 2: Insert a New Module

    1. In the VBA editor, click Insert in the top menu.
    2. Select Module to create a new module.

    Step 3: Write the VBA Code to Combine Sheets

    Here’s a simple VBA code that combines all sheets in your workbook into one:

    Sub CombineSheetsWithDifferentColumns()
        Dim ws As Worksheet
        Dim masterSheet As Worksheet
        Dim lastRowMaster As Long
        Dim lastRowSource As Long
        Dim lastColSource As Long
        Dim pasteRow As Long
        
        ' Create or Clear a MasterSheet for combined data
        On Error Resume Next
        Set masterSheet = Sheets("MasterSheet")
        On Error GoTo 0
        
        If masterSheet Is Nothing Then
            Set masterSheet = Worksheets.Add
            masterSheet.Name = "MasterSheet"
        Else
            masterSheet.Cells.Clear
        End If
        
        pasteRow = 1 ' Row to start pasting in the MasterSheet
        
        ' Loop through each worksheet in the workbook
        For Each ws In ThisWorkbook.Worksheets
            ' Skip the MasterSheet itself
            If ws.Name <> "MasterSheet" Then
                ' Find the last row and column with data in the source sheet
                lastRowSource = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                lastColSource = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                
                ' If there is data, copy it
                If lastRowSource > 1 Then
                    ' Copy data from the current sheet to the MasterSheet
                    ws.Range(ws.Cells(1, 1), ws.Cells(lastRowSource, lastColSource)).Copy
                    masterSheet.Cells(pasteRow, 1).PasteSpecial xlPasteAll
                    
                    ' Move to the next row in MasterSheet
                    pasteRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row + 1
                End If
            End If
        Next ws
        
        ' Optional: Autofit columns to adjust widths
        masterSheet.Columns.AutoFit
        
        MsgBox "All sheets have been combined into 'MasterSheet'."
    End Sub

    Step 4: Run the Macro

    1. Close the VBA editor and return to Excel.
    2. Press Alt + F8 to open the Macro dialog box.
    3. Select CombineSheets and click Run.

    Step 5: Review the Combined Data

    • The macro will create a new sheet named “MasterSheet” where all the data from the other sheets is combined.
    • If the “MasterSheet” already exists, it will be cleared and refilled with the new data.

  • To Combine Multiple Workbooks in Excel

    You can use VBA to combine workbooks and add the workbook name in first column and sheet name as the second column. Here’s a basic script:

    1. Open Excel and press Alt + F11 to open the VBA editor.
    2. Insert a new module by right-clicking on any existing module and selecting Insert > Module.
    3. Paste the following VBA code:
    Sub CombineWorkbooksWithNames()
    Dim FolderPath As String
    Dim FileName As String
    Dim WS As Worksheet
    Dim TargetSheet As Worksheet
    Dim LastRow As Long
    Dim SourceWB As Workbook
    Dim i As Long

    ' Set the path to the folder containing the workbooks
    FolderPath = "C:\Your\Folder\Path\"

    ' Create a new sheet for combined data
    Set TargetSheet = ThisWorkbook.Sheets.Add
    TargetSheet.Name = "CombinedData"
    TargetSheet.Cells(1, 1).Value = "WorkbookName"
    TargetSheet.Cells(1, 2).Value = "SheetName"

    ' Start looping through all Excel files in the folder
    FileName = Dir(FolderPath & "*.xls*")
    Do While FileName <> ""
        Set SourceWB = Workbooks.Open(FolderPath & FileName)
        
        ' Loop through all sheets in the workbook
        For Each WS In SourceWB.Sheets
            LastRow = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            
            ' Copy data from the source sheet to the target sheet
            WS.UsedRange.Copy Destination:=TargetSheet.Cells(LastRow, 3)
            
            ' Add workbook name in the first column
            TargetSheet.Cells(LastRow, 1).Resize(WS.UsedRange.Rows.Count, 1).Value = SourceWB.Name
            
            ' Add sheet name in the second column
            TargetSheet.Cells(LastRow, 2).Resize(WS.UsedRange.Rows.Count, 1).Value = WS.Name
        Next WS
        
        SourceWB.Close False
        FileName = Dir
    Loop
End Sub

    4. Modify the FolderPath to match the location where your workbooks are stored.

    5. Run the macro by pressing F5.

  • Why Learn Javascript :

    Why Learn Javascript :

    Learning JavaScript first has a couple of advantages while working with beginners in programming or web development:

    Versatility:
    JavaScript is an versatile language, holding its hand into both the frontend and backend areas. This language is what sites use to make it interactive, also being highly adopted in frameworks like React, Angular, and Vue while creating dynamic user interfaces. The Node.js backend environment allows JavaScript to extend its functionality to server-side operations.

    Instant Use:
    One can instantly initiate the process of building and interacting with web pages by the use of JavaScript. That alone may be quite motivating and cements learning when you see how your code falls into place in real time.

    High Demand:
    JavaScript is one of the highly demanded programming languages in the world currently. Learning it opens more career opportunities in web development and app development, among others.

    Community and Resources:
    JavaScript has a huge, very live community. This means there are a ton of resources, tutorials, and libraries for learning and problem solving.

    Foundational to Other Technologies:
    JavaScript is essentially the basis for most modern web technologies. This means learning it will ease the process of picking up related languages and frameworks, further expanding your versatility as a developer.

    Ease of Learning:
    JavaScript is quite easy to learn for most individuals. Since there are no overwhelming setups or environments one might need, one can simply install a browser and a text editor to get started.

    Integration with HTML and CSS: JavaScript integrates very well with HTML and CSS, key building blocks of the web. Learning it in tangent with these other technologies elaborates in full how websites work.