Copy Excel text to PowerPoint without copying the text inside the parenthesis-CodePudding

I have written a code (excel VBA) to output powerpoint report. Part of the code is to create a table, then supply the data into it from the excel. My problem now is copying a certain data from excel. I do not want to copy any information inside the parenthesis as well as the parenthesis itself. For example, the data is the name of the employee together with their employee ID. I just need the employee's name. Do you know how to do it?

Below is part of the code. It's quite long since im still new in VBA coding. From the code below, the last part .Text = Range("F1") is the code where I will copy the data I will translate to the powerpoint.

 Set myShape = myPresentation.Slides(2).Shapes.AddTable(10, 4, 50, 100, 800)
             myShape.Table.Rows.Add
             myShape.Height = 0
            With myShape.Table
                
            .Cell(1, 1).Merge MergeTo:=.Cell(1, 2)
            .Cell(1, 2).Merge MergeTo:=.Cell(1, 3)
            .Cell(1, 3).Merge MergeTo:=.Cell(1, 4)
         
        With .Cell(1, 4).Shape
         With .TextFrame.TextRange
         .Text = "General Information"
         .Font.Size = 13
         .Font.Name = "Arial"
         .Font.Color = RGB(0, 0, 0)
         
            End With
        End With
            With .Cell(2, 1).Shape
        With .TextFrame.TextRange
         .Text = "FA Site"
         .Font.Size = 13
         .Font.Name = "Arial"
         .Font.Color = RGB(0, 0, 0)
            End With
        End With
            
            With .Cell(2, 2).Shape
        With .TextFrame.TextRange
         .Text = "Singapore"
         .Font.Size = 13
         .Font.Name = "Arial"
         .Font.Color = RGB(0, 0, 0)
            End With
        End With
            
            With .Cell(2, 3).Shape
        With .TextFrame.TextRange
         .Text = Range("F1")
         .Font.Size = 13
         .Font.Name = "Arial"
         .Font.Color = RGB(0, 0, 0)
            End With
        End With

I dont know how to write the VBA code for this problem. I hope someone can help me.

CodePudding user response：

As a possible answer, depending on your reply to my comment earlier, here's a function that will return the original text you pass to it with any text between open and close parentheses removed.

It's not very robust; you'll want to add further checks to make sure that, for example, there ARE both open and close parens in the string.

Function RemoveParens(sText As String) As String

    Dim sTemp As String
    Dim lParenStart As Long
    Dim lParenEnd As Long
    Dim sParentheticalText As String
    
    lParenStart = InStr(sText, "(")
    lParenEnd = InStr(sText, ")")
    
    sTemp = Mid$(sText, 1, lParenStart - 1)
    sTemp = sTemp & Mid$(sText, lParenEnd   1)
    
    RemoveParens = sTemp
    
End Function

CodePudding user response：

Here is another option. It has it's pros and cons.

Pros:

*** Will work on multiple strings! Will not remove parts you want to keep in between bracketed areas, and will always remove all bracketed areas.
Will not throw errors if nothing to remove (might also be a con in some situations)
Short easy to use/edit function

Cons:

You need to have a cell free to use for the calculations (Either A1 on a hidden sheet, or some cell you know is free
Slow for large datasets, This takes about half a second per string on a slow work computer. if you're only dealing with =< 50 strings, great! otherwise this would take a few minutes to calculate. This may actually be the slowest way to do this calculation.

Option Explicit
Sub TextToStrip()
    Debug.Print StripOutParaText("Test (ouch) and Some ( dog) Number 1")
    ' Output = "Test and Some Number 1"
End Sub
Function StripOutParaText(RefText As String) As String
    Dim RG As Range
    Set RG = Worksheets("Data").Range("A1")
    RG.Value = RefText
    RG.Replace " (*)", "", xlPart
    StripOutParaText = RG.Value
End Function