Exporting data from spreadsheet in excel to a custom report in word.

  • Hello,

    I have created a spreadsheet in excel with client data and situation/recommendations. What I do is go into excel enter the data. My spreadsheet then has all the calculations and charts for me.

    Now, I want to create a document/template/process which will take this info and populate predetermined reports in word.

    For example, I would like to have to reports 1. Custom report single, 2. Custom Report Married, 3. Custom Report Married with Children.

    If I choose Custom Report Married, then that report (that I have previously designed will popular my client's info aswell as their situation/recommendations.

    Can anyone help me with this please??

  • Can't upload sample file as I don't have one yet.

    However, I don't know if I can use mail merge or not. I have graphs and data that I want transferred into Word.

    All I know is that I was using a program once and you could choose "custom report". Then you would have to pick a file name (ie. report.doc). Then when you hit GO MS Word would open with Report.Doc but it put the custom data in predetermined locations.

    I could edit it (I can't remember how) I think it was with the fields... I would put a special field name in a position in the document where I wanted the data entered.

    I don't know how to do this from Excel to Word though.

  • You want to know how to do something, but you're not sure how it was done.

    I'm not sure I can tell you. Hee hee. :)

    Just busting your chops...

    I think you might want something like this:

    Check out the sample file.

  • Thank you, looks good I will try it and see if it works. Are you suggestion I put a button in there and when clicked it executes this code??

    Also, will this transfer the whole worksheet or individual cells. There are some areas where I am going to use the range of cells to populate a table and in other areas I will be just putting a cells information mid sentence. Aswell I was to be able to put a chart in a predetermined spot in the word doc.

    Does this make sense?

  • Yes, it does.

    However...I don't want to seem rude or snotty, but if you want help, you're going to have to help yourself.

    You could not provide a sample file, and now you're asking what the sample file does. Did you download it and check it out? It does everything you want, according to your specifications, which you place on an additional worksheet.

    I don't know if you want a button to run the code or if you want to run it from a shortcut key--that's entirely up to you.

  • Ok... I did some samples.

    I'm sorry for not having them originally but it just made so much sense what I wanted to do, I thought it might be a very simple thing.

    Anyways, my excel spreadsheet contains a bunch of data... I have a document for word of how I want it to look (a dummy file) and I want to populate the areas <name>, <address1>, etc... I don't know how to setup the word document properly because it opens the file and nothing happens. I have cut and pasted the code to module1 of the spreadsheet but the only way I can get it to run is to goto the Run menu and select Run (because I don't know how to get a command button in a worksheet.

    Hope this helps... If not just ask what I am trying to do.

    P.S. No offense taken with the help myself thing.

  • Okay, cool.

    Step 1:

    At each place where you want the name to be inserted into the Word document, you must Insert-Bookmark and put a bookmark name, such as CustName.

    At each place where you want the person's address to appear in the Word doc, you must Insert-Bookmark and put a bookmark name, such as CustAddress.

    Place a bookmark at each location in the Word document where you want a piece of data from the Excel file to appear. You must also place a bookmark at the insertion point where you want the chart.

    Step 2:

    You must insert named ranges into your Excel file. Give the named ranges the same name that you gave the bookmarks in the Word file. Select the name cell and Insert-Name-Define and call it CustName. Select the cells with the address and Insert-Name-Define and call it CustAddress, and so on.

    Step 3:

    Add a worksheet to your Excel file. The worksheet is intended to provide instructions to the automation. It must be set up exactly as it is set up in the sample files I uploaded, and the worksheet is called Summary. You must have the same column headings, but you will have different data.

  • Okay, Thank you it does do EXACTLY what I want... However, I can't figure out how to name a chart in excel ie. Book2 in your example. How do you assign that name. If I select the chart and go to Insert-Name "Name" isn't there.

  • Okay... I think I am starting to figure it out. I had what you gave me working but I am trying to understand it now. I have attached my new files and I was wondering if you could tell me what I am doing wrong please. I finally got it to import some stuff to WORD but it is doing the full sheet and not the individual cells that I assigned Names to.

    STEP 1:

    I selected cell A1 and went to Insert - Names - Define and typed "FullName".

    STEP 2:

    I created a Bookmark in my report.doc.

    STEP 3:

    I ran the program and it inserts the COMPLETE sheet not just cell A1.

    If you could please run it and tell me what I did wrong.
    P.S. I set it up to run from "C:\" (root dir).

    Thanks. I appreciate all the help too.

  • Okay. Sorry to take so long. I don't think I got a notif, but I might have and just too busy at the time to look, never marked unread. :(


    I tried fixing it.
    Thought I did.
    Still didn't work--pasted a big area of the spreadsheet.

    We're looking into the problem.


  • Good Stuff Here, Dreamboat

    This looks like a really great program Dreamboat!! I would love to borrow it for something I'm working on, but I don't seem to be able to compile it. It works fine out of the box, but I can't make any changes without messing it up. What can you tell me about this "wdGoToBookmark" command? That seems to be where I keep hanging myself...

    AA :thanx:

  • It emulates Edit-->Go to-->Bookmark.

    Try inserting a bookmark into a doc.
    Call the bookmark "book1"

    Then, record a macro of yourself hitting Edit-->Go to-->Bookmark and choose book1.

    Stop recording...

    When you look at the code, delete all the nonsense part, you get this:

    Sub Macro3()
        Selection.GoTo What:=wdGoToBookmark, Name:="book1"
    End Sub

    If you're getting an error, I suspect you've got the name wrong or you never created a bookmark by that name in the first place.

  • Dreamboat,

    Thank you for trying... I await your response.

    Also, I think I forgot to ask you how to define a name to a chart object. All I did was highlight the cells/range that was directly under the chart... Is that right??


  • Mr Oxide-
    I'm quite new to Word, Excel, and VBA, so I don't know that I should give any poor soul advice, but I'm pretty sure all you have to do to name a cell is to be in the cell with your cursor, and go to "insert->name->define" and follow the directions. I hope I am not intruding, or telling you the obvious and misunderstanding your question. About the program Dreamboat wrote -- I'm liking it very much and trying to modify it a bit to extract data from a cingle cell (or actually, from any of a feild of cells.) Is there a better way to do this than with bookmarks? I'd really like to keep lists of data in Excel, and then be able to extract chunks (or cells) of data for importation into Word. Has anyone got any other ideas?
    I appreciate everyone's help...

    AA :thanx:

  • No Problem AC. I have already defined a name to field(s) of cells and it is still importing the whole sheet for some reason... If I am not mistaken it is happening to Dreamboat as well.

  • Quote

    About the program Dreamboat wrote

    Let's make it clear now.
    Dreamboat didn't write that code.


    Dreamboat had someone else write it.


    But...as for AC's Q: If not bookmarks, how will you define where in the Word document the Excel data should be placed?

    And yes, ZO: it's happening to me too. Working on getting a fix!

  • Dreamboat-

    I don't mind using bookmarks. It actually looks quite simple, which is what I want. I just have a problem in the code (well, in my understanding of it) where it "grabs" the item bookmarked. It's all or nothing, as you see, and I haven't figured out how to adjust it yet. I've tried putting the bookmark names all in one column with named ranges, and storing in a two-column name-bookmark lookup, but either way, I can't catch a single cell (or a named smaller group) from a given location on the page. As for the Word doc, I don't know of any way to do it except bookmarks. I'm starting to wonder if I should use a form, but that would be another new one on me. Whew. I'm just picking the brains of genius here, hoping for any help I can get finding great ways to link Excel, Word, and Outlook too, when I get there. By the way, I've looked at a lot of your stuff. You really are great, and I appreciate what I've learned from you in the last three weeks.

    AA :thanx:

  • Dreamboat & AC:

    Have taken out the reference to "UsedRange" and replaced it with Range(BookMarkName) and that works.

    When I used the AppWrd.Selection.Paste I noticed that it was copying the complete cell including the properties of it and creating a table when it was pasted in Word.

    Then, changed to:

    Appwrd.Selection.Goto What:=wdGotoBookmark, Name:=BookmarkName

    ' And to paste it instead of using the .paste I did this...
    AppWrd.Selection = Sheets("Summary").Range(BookmarkName)

    That worked.


    It doesn't copy charts... I was wondering how to copy just the chart object.

    Hope you followed me... (The code is from memory because on a different computer if wrong let me know and I'll update it).

Participate now!

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