none
Edit text in text box using VBA RRS feed

  • Question

  • Using MS Office 2007.  I have a procedure that copies a multi line address from a single cell in an excel workbook into a text box in a word document.  Because I use PasteSpecial with DataType as wdPasteText, it is pastes the address into the text box with quotes ("") around the whole address.  I tried using DataType of wdPasteRTF, but that gave me double spacing, though it got rid of the quotes.  So I shall have to edit out the quotes from the text in the text box using VBA.  While I can find how to address the text in the body of a word document, I can't find the syntax to address the text in a shape. 

    When I find how to do address the text in the text box, I then need to find the first and last characters, find if they are quotes, and if so delete them.

    I'd be grateful for any guidance on how to deal with these two problems

    Monday, March 24, 2014 9:57 PM

Answers

  • Personally I wouldn't use copy & paste to get the content of the cell in the text box in the first place. Set the textbox text range as the value of the cell e.g. put the value of cell B2 from the named worksheet into the first textbox. If the worksheet doesn't have quotes in that cell, then neither will the text box.

    Dim xlApp As Object
    Dim xlbook As Object
    Const strWorkbookName = "C:\path\workbook.xlsx"
        Set xlApp = GetObject(, "Excel.Application")
        If Err Then
            Set xlApp = CreateObject("Excel.Application")
        End If
        On Error GoTo 0
        Set xlbook = xlApp.Workbooks.Open(Filename:=strWorkbookName)
        ActiveDocument.Shapes(1).TextFrame.TextRange.Text = xlbook.sheets("SheetName").Cells(2, 2)
        xlbook.Close 0

    If the cell does have the quotes then use the replace function as you write the data e.g.

    Dim xlApp As Object
    Dim xlbook As Object
    Dim strCell As String
    Const strWorkbookName = "C:\path\workbook.xlsx"
        Set xlApp = GetObject(, "Excel.Application")
        If Err Then
            Set xlApp = CreateObject("Excel.Application")
        End If
        On Error GoTo 0
        Set xlbook = xlApp.Workbooks.Open(Filename:=strWorkbookName)
        strCell = xlbook.sheets("SheetName").Cells(2, 2)
        ActiveDocument.Shapes(1).TextFrame.TextRange.Text = Replace(strCell, Chr(34), "")
        xlbook.Close 0


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by AndyColRomsey Tuesday, March 25, 2014 3:07 PM
    Tuesday, March 25, 2014 2:42 PM

All replies

  • Could you simply not use Replace function?

    ? Replace("""Hello World""", """", "")
    Hello World


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Tuesday, March 25, 2014 11:00 AM
  • Personally I wouldn't use copy & paste to get the content of the cell in the text box in the first place. Set the textbox text range as the value of the cell e.g. put the value of cell B2 from the named worksheet into the first textbox. If the worksheet doesn't have quotes in that cell, then neither will the text box.

    Dim xlApp As Object
    Dim xlbook As Object
    Const strWorkbookName = "C:\path\workbook.xlsx"
        Set xlApp = GetObject(, "Excel.Application")
        If Err Then
            Set xlApp = CreateObject("Excel.Application")
        End If
        On Error GoTo 0
        Set xlbook = xlApp.Workbooks.Open(Filename:=strWorkbookName)
        ActiveDocument.Shapes(1).TextFrame.TextRange.Text = xlbook.sheets("SheetName").Cells(2, 2)
        xlbook.Close 0

    If the cell does have the quotes then use the replace function as you write the data e.g.

    Dim xlApp As Object
    Dim xlbook As Object
    Dim strCell As String
    Const strWorkbookName = "C:\path\workbook.xlsx"
        Set xlApp = GetObject(, "Excel.Application")
        If Err Then
            Set xlApp = CreateObject("Excel.Application")
        End If
        On Error GoTo 0
        Set xlbook = xlApp.Workbooks.Open(Filename:=strWorkbookName)
        strCell = xlbook.sheets("SheetName").Cells(2, 2)
        ActiveDocument.Shapes(1).TextFrame.TextRange.Text = Replace(strCell, Chr(34), "")
        xlbook.Close 0


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by AndyColRomsey Tuesday, March 25, 2014 3:07 PM
    Tuesday, March 25, 2014 2:42 PM
  • As always, Graham, a thoroughly helpful answer.

    I adapted the line: 

    ActiveDocument.Shapes(1).TextFrame.TextRange.Text = xlbook.sheets("SheetName").Cells(2, 2)

    from your code into my code and it did precisely what I wanted without all the faffing about with copy and paste.

    With many thanks

    Andy C

    Tuesday, March 25, 2014 3:18 PM