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

Can you upload sample files?

Why can't you use a regular old mail merge?

What version of Office do you have?

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:

'You must set a reference to Microsoft Word Object Library from Tools | References
 
Option Explicit 
 
Sub ExportToWord() 
     
    Dim appWrd As Object 
    Dim objDoc As Object 
    Dim FilePath As String 
    Dim FileName As String 
    Dim x As Long 
    Dim LastRow As Long 
    Dim SheetChart As String 
    Dim SheetRange As String 
    Dim BookMarkChart As String 
    Dim BookMarkRange As String 
    Dim Prompt As String 
    Dim Title As String 
     
     'Turn some stuff off while the macro is running
    Application.ScreenUpdating = False 
    Application.EnableEvents = False 
    Application.DisplayAlerts = False 
     
     'Assign the Word file path and name to variables
    FilePath = ThisWorkbook.Path 
    FileName = "WorkWithExcel.doc" 
     
     'Determine the last row of data for our loop
    LastRow = Sheets("Summary").Range("A65536").End(xlUp).Row 
     
     'Create an instance of Word for us to use
    Set appWrd = CreateObject("Word.Application") 
     
     'Open our specified Word file, On Error is used in case the file is not there
    On Error Resume Next 
    Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName) 
    On Error Goto 0 
     
     'If the file is not found, we need to end the sub and let the user know
    If objDoc Is Nothing Then 
        MsgBox "Unable to find the Word file.", vbCritical, "File Not Found" 
        appWrd.Quit 
        Set appWrd = Nothing 
        Exit Sub 
    End If 
     
     'Copy/Paste Loop starts here
    For x = 2 To LastRow 
         
         'Use the Status Bar to let the user know what the current progress is
        Prompt = "Copying Data: " & x - 1 & " of " & LastRow - 1 & " (" & _ 
        Format((x - 1) / (LastRow - 1), "Percent") & ")" 
        Application.StatusBar = Prompt 
         
         'Assign the worksheet names and bookmark names to a variable
         'Use With to group these lines together
        With ThisWorkbook.Sheets("Summary") 
            SheetChart = .Range("A" & x).Text 
            SheetRange = .Range("B" & x).Text 
            BookMarkChart = .Range("C" & x).Text 
            BookMarkRange = .Range("D" & x).Text 
        End With 
         
         'Tell Word to goto the bookmark assigned to the variable BookMarkRange
        appWrd.Selection.Goto What:=wdGoToBookmark, Name:=BookMarkRange 
         
         'Copy the data from Thisworkbook
        ThisWorkbook.Sheets(SheetRange).UsedRange.Copy 
         
         'Paste into Word
        appWrd.Selection.Paste 
         
         'Tell Word to goto the bookmark assigned to the variable BookMarkChart
        appWrd.Selection.Goto What:=wdGoToBookmark, Name:=BookMarkChart 
         
         'Copy the data from Thisworkbook
        ThisWorkbook.Sheets(SheetChart).ChartObjects(1).Copy 
         
         'Paste into Word
        appWrd.Selection.Paste 
    Next 
     
     'Turn everything back on
    Application.ScreenUpdating = True 
    Application.EnableEvents = True 
    Application.DisplayAlerts = True 
    Application.StatusBar = False 
     
     'Let the user know the procedure is now complete
    Prompt = "The procedure is now completed." & vbCrLf & vbCrLf & "www.VBAExpress.com" 
    Title = "Procedure Completion" 
    MsgBox Prompt, vbOKOnly + vbInformation, Title 
     
     'Make our Word session visible
    appWrd.Visible = True 
     
     'Clean up
    Set appWrd = Nothing 
    Set objDoc = Nothing 
     
End Sub

Check out the sample file.

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.

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

Anyway...

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.

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:

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!

Here are some additional help locations.
I did something similar and Andy Pope helped me.
To produce and name each chartr use the attached code (modified for your data).
To add to word mail merge use this link to the the thread to find what we did for me:
http://www.ozgrid.com/forum/showthread.php?t=22835

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: