+ Reply to Thread
Results 1 to 8 of 8

Excel - Word Automation - Copying data

  1. #1
    Registered User
    Join Date
    02-01-2017
    Location
    Leeds
    MS-Off Ver
    2010
    Posts
    12

    Excel - Word Automation - Copying data

    Hello,

    Firstly, thank you for taking the time to review my post.

    My VBA level is beginner, so please go easy.

    Can VBA make it possible to search for a "header" within the sheet, once found copy the data in the adjacent cell and paste it into a word document...again in a defined area matching the previous header.

    The scenario being;

    I currently have a spread sheet used by a team of engineers to review pieces of equipment at locations. Once this is completed I have to amend into a client friendly word document which has set headers / styles. This currently involves me copying and pasting data from one area to another over 181 times and I'm sure there must be an easier way.

    The data being copied is in text format.

    Apologies if this doesn't make sense, I have tried to detail as much as I can however I will be active between working hours so please fire away with questions.

    Thanks in advance.

    Michael

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Excel - Word Automation - Copying data

    Hi Michael

    it sounds like this could be done using VBA, but some more info would help. When you talk of a "header" in Excel, what do you mean? It is possible to set up a repeating header in Excel in page layout, but from your description it sounds like it is in a particular cell (perhaps you have the word "header" in cell A1 and the text you want to copy in cell A2, for example).

    Similarly, in the Word document, how is the area where you want the text pasted defined. Is it in a named area of the document (eg a bookmark)? Or simply that you want to apply a particular formatting style to the text once it is pasted?

    VBA can be good at this kind of problem but it needs to be told what to do and where - copy text from X, paste it to Y.

    If you could upload copies of the excel file and word documents (or similar dummies, if they are confidential) it could help.

  3. #3
    Registered User
    Join Date
    02-01-2017
    Location
    Leeds
    MS-Off Ver
    2010
    Posts
    12

    Re: Excel - Word Automation - Copying data

    Hi Nicky,

    Many thanks for your response. ( I'm hoping my attachments post, for some reason it is not allowing me to attach anything, when I select "Go advanced" & Click on the attachment link I just get a white bubble with no option to browse....this may be a reflection of the server migration unless I am missing something.

    I have uploaded two dummy documents for reference. (Probably should have done this in the first place)

    Document A is the excel document I need to copy the data from. The headers within this document will match the headers within the word document (Identical).

    Within the document there will be 20(ish) main headers (Found in column B) of these main headers there will be sub headers, again identically found in the word document.

    Example

    Main Header Electricity
    Sub Header PAT Test
    Sub Header Wiring checks

    Main Header Water
    Sub Header Water temperature check
    Sub Header Water pressure test

    Document B

    The word document which excel is to transfer the data too. The headers/sub headers within this document will match those in excel.

    I need VBA to search for the sub Header, and then copy the data from column D into the matching sub header on the word document.

    Hope this makes slightly more sense, any questions please ask.

    Many thanks,
    Michael
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Excel - Word Automation - Copying data

    Hi
    this could get you started
    I have assumed
    • the Word document you want to copy into is open, and the only one open in Word
    • the main headings to copy start in cell C21, with style Header 1 applied in Word
    • you only want to copy the next header 1 when it is different from the one above
    • you want to copy the cells from D21 on as sub-headers applying style "Header 2" in Word
    • you want the contents of cells E21 copied below this in "Normal" Style
    • this continues until the first empty cell in column C

    (note I have used the actual cell references from your sheet - for some reason your have labelled column C as B, etc)

    Important: to run Word from excel you must add its reference library. in your VBA module select tools > references > and tick Microsoft word object library

    this VBA should get you started, at least:

    Please Login or Register  to view this content.
    Last edited by NickyC; 02-02-2017 at 06:53 AM.

  5. #5
    Registered User
    Join Date
    02-01-2017
    Location
    Leeds
    MS-Off Ver
    2010
    Posts
    12

    Re: Excel - Word Automation - Copying data

    Hi Nicky,

    Thank you so much. It work's perfectly with only a small error.

    It runs 151 rows down, and then presents an error in this line of data;

    WDApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False


    There are 181 rows in total so already saving me significant amounts of time.

    Any ideas?

  6. #6
    Registered User
    Join Date
    02-01-2017
    Location
    Leeds
    MS-Off Ver
    2010
    Posts
    12

    Re: Excel - Word Automation - Copying data

    Hi Nicky,

    I checked where it was pulling the data from to see if anything stood out, as it seemed strange the loop was working until this point.

    It was due to there being no data in the cell. I saved & re-ran and it worked all the way through.

    Is there any way for VBA to make a decision to move on in the event the cell has no data?

    I honestly can't thank you enough by the way !!

  7. #7
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Excel - Word Automation - Copying data

    Hi Michael
    yes, I wrote the code that way because but there needs to be a trigger for the macro to stop. One way you could do that is to stop when there is nothing in column E (assuming this is never blank), not column C. To do that, change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    alternatively, I could tweak the code so the macro runs until the last used row in the worksheet, but that could cause you problems if you have anything in rows below your main table.

    you might also want to consider what you want to do when a cell in column C is blank. with the adjustment above, the macro will copy the content of every cell in Column C that is different to the cell above it, so a blank cell in Excel will result in a blank line in your Word document, a sub heading and text below that, then a new Heading 1 style header below that (assuming the next cell in column C contains text).

  8. #8
    Registered User
    Join Date
    02-01-2017
    Location
    Leeds
    MS-Off Ver
    2010
    Posts
    12

    Re: Excel - Word Automation - Copying data

    Perfect.

    All done - Coding works perfectly.

    Thank you so much for your help, first time using this forum and you have made the experience fantastic.

    I will mark as solved when I find out how!

    Many thanks,
    Michael

+ 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. [SOLVED] Copying data from Excel to Word
    By mango2015 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 03-23-2016, 12:07 PM
  2. Copying data from excel to word
    By tessda in forum Word Formatting & General
    Replies: 1
    Last Post: 03-12-2016, 10:41 PM
  3. Copying Excel Data to Word
    By Flower R in forum Excel General
    Replies: 2
    Last Post: 12-28-2008, 07:20 PM
  4. RE: EXCEL, WORD Automation
    By lumpjaw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2006, 04:15 PM
  5. [SOLVED] importing Word table data into Excel - solutions for automation
    By Han in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2006, 07:30 PM
  6. [SOLVED] Automation of Excel Data to Word (ref: Galimi answers)
    By Adam in forum Excel General
    Replies: 3
    Last Post: 02-28-2005, 01:06 PM
  7. [SOLVED] Data Automation from Excel to Word
    By Adam in forum Excel General
    Replies: 3
    Last Post: 02-22-2005, 11:06 AM

Tags for this Thread

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