Copy cell from Excel worksheet to matching Word document

Hi there everyone.

I have 274 Word documents which were created by running a command on our Linux ZenWorks server. The command created a new Word Document for every software bundle we have and put the related actions into the document (for some reason the name of the software and its location are not in the Word doc.). The files are named from the software name, ex:Curam GISS CIARA 4.doc, Curam GISS Development 4.doc.....etc.

I also have one Excel Worksheet that has 275 rows, one for each software bundle. Each row has information which was not included when I made the Word Documents.

I'd like to loop through my Excel worksheet and for every matching cell A2 - A275 (Name of software), copy/paste contents from cells A2-A275 and E2-E275 (Name and location of software) into the corresponding Word Document at the top of the document.

Re: Copy cell from Excel worksheet to matching Word document

This looks awesome, thank you. My original post/question may have been a little misleading. The source Excel document has a column for location of software bundle. This isn't really the file location of the document, but the location on our Linux Server as to where the bundle information lives, ex:
[TABLE="width: 610"]

/Bundles/My Applications/Office/ArcGIS ArcReader9.1

/Bundles/My Applications/Applications/ASG Document Direct 4.3

/Bundles/My Applications/Applications/ASG Document Direct 4.3 Authorization Code Update

/Bundles/My Applications/Applications/ASG ZENA Client

/Bundles/My Applications/Communicate/AT&T Aircard

/Bundles/My Applications/Applications/Attachmate Reflection for the Web

/Bundles/My Applications/Applications/Avery Wizard

[/TABLE]

So, I really don't need the file name/location inserted into the word document, but the corresponding data from column E.

I'm guessing I can modify your code

'// Fairly obvious             
   With doc.Content


      '// Add a new paragraph before
      .InsertParagraphBefore 


      '// Add Filename before existing text      
      .InsertBefore StrConv(r.Value & "\" & r.Offset(0, 1).Value, vbUpperCase)              
   End With

to something like:

.InsertBefore (grab content of column E from matching row)

Re: Copy cell from Excel worksheet to matching Word document

Thanks again for your quick help. I was able to add a little and get pretty much what I had wanted.

This puts at the top of the Word Documents the contents of cell E, then adds a line return, then puts the file name and path, and then another line return.

.insertbefore StrConv(r.Value & "\" & r.Offset(0, 1).Value & Chr(11), vbUpperCase)
                .insertbefore StrConv(Chr(11), vbProperCase)
                .insertbefore StrConv(r.Offset(0, 5).Value & "/" & r.Offset(0, 1).Value & Chr(11), vbProperCase)

Re: Copy cell from Excel worksheet to matching Word document

I know, I ended up with some dirty code. But, using the .insertbefore StrConv it made me put something at the end. I just choose vbUpperCase because I thought it wouldn't hurt anything.

Oh, I can tell you that a Proper Cased Chr(11) is not bolded. I haven't been able to figure out how to bold the inserted text without having the entire document turn bold. :whip::wowee:

Re: Copy cell from Excel worksheet to matching Word document

Thanks again for all of the help. I figured out how to make bold the part I needed to be in bold. I think I've also cleaned up the code so I don't have a line feed as vbUpperCase. Here is the final code.

Sub x()
     
    Dim r As Range
    Dim wrd As Object '// Word.Application
    Dim Doc As Object '// Word.Document
     
    Set wrd = CreateObject("Word.Application")
     
     '// change to Suit - This is an example of how to do it
     '// You need to apply some brainpower too
    For Each r In Range("A1:A280")
         
         '// You also need to adjust the offset where the filename is stored
         '// This checks if the document actually exists
        If Dir$(r.Value & "\" & r.Offset(0, 1).Value & ".doc") <> vbNullString Then
             
             '// If so, open in the instance of Word
            Set Doc = wrd.Documents.Open(r.Value & "\" & r.Offset(0, 1).Value & ".doc", AddToRecentFiles:=False)
             
             '// Fairly obvious
            With Doc.Content
                 '// Add a new paragraph before
                .InsertParagraphBefore
                 
                 '// Add Filename before existing text
                .insertbefore StrConv(r.Value & "\" & r.Offset(0, 1).Value & Chr(13) & Chr(10), vbUpperCase)
                .insertbefore StrConv(r.Offset(0, 5).Value & "/" & r.Offset(0, 1).Value & Chr(13) & Chr(10), vbProperCase)
                Doc.paragraphs(4).Range.Bold = True
                
            End With
             
             '// Save it
            Doc.Save
             '// and close
            Doc.Close
             
             '// cleanup
            Set Doc = Nothing
             
        End If
         
    Next
     
     '// More tidy ups
    wrd.Application.Quit
    Set wrd = Nothing
     
End Sub