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


    Not 100% sure what your set up is (I really am having a dumb day...).


    The following takes a directory from Col A and a File name from Col B and if that file exists, opens it in Word, inserts the full file name at the start of the document and saves...


    It currently works in Cols A & B only. Should be easy enough for you to modify.



    *EDIT* Finally figured out what you want.


    The name of the package is in Col A and the filename in Col E... Not matter, still should be easy enough for you to figure...

  • 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"]

    [tr]


    [td]

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

    [/td]


    [/tr]


    [tr]


    [td]

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

    [/td]


    [/tr]


    [tr]


    [td]

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

    [/td]


    [/tr]


    [tr]


    [td]

    /Bundles/My Applications/Applications/ASG ZENA Client

    [/td]


    [/tr]


    [tr]


    [td]

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

    [/td]


    [/tr]


    [tr]


    [td]

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

    [/td]


    [/tr]


    [tr]


    [td]

    /Bundles/My Applications/Applications/Avery Wizard

    [/td]


    [/tr]


    [/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


    to something like:

    Code
    .InsertBefore (grab content of column E from matching row)
  • Re: Copy cell from Excel worksheet to matching Word document


    My fault, just couldn't read your post clearly. But yes, just use whatever logic you want to use to determine what information gets inserted.

  • 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.




    Code
    .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


    That's an interesting one - just what does a Proper Cased Chr$(11) (Shift-Enter in Word) look like...? :)


    Still, as long as it works...

  • 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


    If you replace the last Chr(11) with a full paragraph mark, you can select the first paragraph and set the Font to bold. I'm just on the way out the door right now, but if you haven't sorted it by tomorrow, I'll include that for you...


    It would be handy if you included the actual code you have now just so we're both looking at exactly the same thing.

  • 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!