+ Reply to Thread
Results 1 to 2 of 2

copying from an excel cell to a named word text box

Hybrid View

  1. #1
    Registered User
    Join Date
    04-21-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    64

    copying from an excel cell to a named word text box

    Hi,

    I am having a bit of trouble getting this code to work. I have broken it down simply to have a single text box in the word document 'Word.docx' and have named it "Test" using the 'plain text content control' I have clicked the design mode all on the developer tab in word and then changed the name to "Test" and saved the document.

    Next I have created a new macro enabled excel spreadsheet and ticked the word box in tools -> reference in the VBA display. In the excel sheet only two cells have values, E8 contains "Hello" and F8 contains "Saved"

    When I run the script it should open the word document, enter "Hello" into the saved text box, rename it "Saved" then save as in both a word doc and pdf.

    So what it is doing is saving the new word doc with the new name however it is not filling the text box nor is it saving a pdf with the same name

    Below is the script i'm trying to run;

    Sub populatesub()'declare variables
        Dim wdApp As Object, wdDoc As Object
        Dim strdocname As String
        Dim fName As String, fPath As String
        Dim ws As Worksheet
    'set values
        Set ws = ThisWorkbook.Sheets("Sheet1")
        fPath = "C:\sample sheet testing\"
        fName = ws.Range("F8").Value
        strdocname = "Word.docx"
    'Word environment
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        wdApp.Activate
        On Error Resume Next
        Set wdDoc = wdApp.Documents.Open(fPath & strdocname)
        If Err.Number > 0 Then Exit Sub
        wdDoc.Activate
    
    
    'update text box
        wdDoc.Shapes("Test").TextFrame.TextRange.Text = ThisWorkbook.Sheets("Sheet1").Range("E8").Value
    
    
    'save the files
        wdDoc.SaveAs Filename:=fPath & fName
        wdDoc.SaveAs2 fPath & fName & "pdf", wdExportFormatPDF
        wdApp.Quit
    
    
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Application.CutCopyMode = False
    End Sub
    Any help would be greatly appreciated

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: copying from an excel cell to a named word text box

    Try this to save as PDF.

    wdDoc.SaveAs2 fPath & fName & ".pdf", FileFormat:=17 'wdExportFormatPDF

    Not sure about the text box.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copying Highlighted Text from a number of word documents to excel sheet macro
    By Jxb1112 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-27-2015, 09:49 PM
  2. Copying Text from MS Word into Text Box in Excel
    By qwe789123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2010, 08:43 PM
  3. Replies: 1
    Last Post: 05-19-2010, 07:20 AM
  4. Text not copying as text from Word to Excel 2003
    By Christine in forum Excel General
    Replies: 4
    Last Post: 03-03-2005, 08:06 PM
  5. [SOLVED] Best way of copying text to Word from Excel
    By TT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-27-2005, 12:06 PM
  6. [SOLVED] Copying Word text to Excel
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1