Building Word Documents based on Individual Excel Rows

[IcePirates]

Hello,
Im seeking help ... I have a VB script that passes information from the first row of an excel document to a word document.

The first row has a "Create Document" button in the 6th cell ("F" column)

On every row there will be a "create document" button that will transfer
the information from that that row to a word document, but only the information in that row.

Im wondering what I would add to my script so that when the "Create Document" button is clicked for that row - the script only builds a word document based on the information in that specific row.
(Any help is appreciated!!)

Here is my code:

Option Explicit
Public Sub TransferData()
    ' This macro transfers the data range "A1:E1" to a table in Word
    ' Constants:
    ' docFullName = The full name of an already existing Word document
    ' Variables:
    ' doc = The Word document (assumed to be empty)
    ' i = A counter (for rows)
    ' j = A counter (for columns)
    ' tbl = A Word table
    ' wdRng = A Word range (the first paragraph of doc)
    ' wks = The worksheet "data" that contains the data range
    ' Const docFullName = "Automation\Word.doc" '
    Dim doc As Object
    Dim i As Long
    Dim j As Long
    Dim tbl As Object
    Dim wdApp As Object 'Only if you require a new document each time
    Dim wdRng As Object
    Dim wks As Worksheet
    ' Assing Word objects 'Only if you require a new document each time
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set doc = wdApp.Documents.Add
    ' Assign variables and objects
    ' Set doc = GetObject(docFullName) 'Only if you want a specific document
    Set wdRng = doc.Paragraphs(1).Range
    Set tbl = doc.Tables.Add(wdRng, 11, 5)
    Set wks = ThisWorkbook.Worksheets("Transmittal")
    ' Transfer the data
    With tbl
        For i = 1 To 1
            For j = 1 To 5
                .Cell(i, j) = wks.Cells(i, j)
            Next j
        Next i
    End With
    ' Save and close doc 'Only if you want a specific document
    ' Call doc.Save
    ' Call doc.Close(False)
    ' Clean
    Set doc = Nothing
    Set wks = Nothing
End Sub

Private Sub CommandButton2_Click()
End Sub

Right now, this will build a document based on row 1 in the Excel document but
when someone add's information in Row 2, then what? I can add another form > button > but what do I add into my script so that when this button on row2 is clicked it only builds a word document based on the information in row 2

[IcePirates]

Would I just keep building Macro's until like line 50,000 and just keep assigning the next button to execute the next row and so on?

There has to be an easier way isnt there?

[Kenneth Hobs]

I would freeze a row or column and put the button there.

For i = ActiveCell.Row To ActiveCell.Row

[RonMcK]

Kenneth,

Using his code would your suggestion change his code as follows?

        With tbl
    For i = ActiveCell.Row To ActiveCell.Row    ' <<your change
        For j = 1 To 5
            .Cell(i, j) = wks.Cells(i, j)
        Next j
    Next i
    End With

How do you get the command button to appear on the line the user selects?

Thanks,

[JKwan]

Kenneth,

Using his code would your suggestion change his code as follows?

With tbl
    For i = ActiveCell.Row To ActiveCell.Row ' <<your change
        For j = 1 To 5
            .Cell(i, j) = wks.Cells(i, j)
        Next j
    Next i
End With

How do you get the command button to appear on the line the user selects?

Thanks,

How about this to get your button onto your active line?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Range("C:C"), Target) Is Nothing Then
        Sheet1.CommandButton1.Top = Target.Top
    End If
End Sub

[Kenneth Hobs]

Ron, I would not put the command button on the user's row. I would put it in the frozen row or frozen column. They would click the row they want to export and then click the button. One trick I used once was to make the button long and thin and put it in the first column and froze that column. This makes it very easy to use.

If one really needs the button to move, I would do something a bit like what JKwan did. e.g.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' If Target.Row = Me.CommandButton1.TopLeftCell.Row Then Exit Sub
    Me.CommandButton1.Top = Range("F" & Target.Row).Top
    Me.CommandButton1.Left = Range("F" & Target.Row).Left
End Sub

Crossposted: http://www.mrexcel.com/forum/showthread.php?t=357019

[IcePirates]

Hey,

Sorry, haven't been able to check this post in a day or so...Let me give a bit further of an explanation...So what-ever row Im on I need a command button for the user to click "Create Document" and a word document gets created based on the information only in that row...

So doing what your suggesting (having a drop-down) for the user to select which row then > having a command button to say "Create Document" and once the button is clicked a word document is created...

So my next question would be...I guess what do I add to my code to do this? I can add a drop down, the command button exists... - but what if Im on line 10,342 then I add information to line 10,343 is line 10,343 automatically just going to get added to this drop down?

Thanks for all the help and input from Kenneth, and everyone else - with this issue!

[Kenneth Hobs]

Just create one CommandButton and put it on that sheet.

Copy the Worksheet code to that sheet by right clicking the sheet's tab, View Code, and paste. As you select each row on that sheet, the Selection event for that sheet fires and move the button to Column H for that row.

Obviously, you need to add the Activecell line that we explained.

[IcePirates]

So what your saying is to, add a command button, and have it so, which ever row Im on the command button automatically moves down to that row?

[Kenneth Hobs]

Yes. I also reset the number of rows in the MSWord table to 1.

This attachment shows the issues resolved in this thread and 168731.

[IcePirates]

Hey,

Fantastic, it works just the way I need, thanks for your help!

My next step is to customize how the Word document displays...At present when the word document is created, the information gets created in a table, do you know how I can remove this table?

Do you have any recommendations for websites that discuss how to customize how my word document is going to display the information? Ive read a bit, and it seems I need to set up book-marks in the word document? But what if the word document is non-existant and is going to be created when the user hits the <Command-Button>...Then how would I customize where the data from the Excel row displays on the word document?

Do you have any recommendations personally, or do you have any website tutorials that may discuss this? Ive included a Word.doc as an attachment and outlined where I want my data to be posted within the word document...

Anyways - let me know what you think! I can always try and put-together something then post back here for code-corrections...but I just wanted to see what your thoughts were on this...

[Kenneth Hobs]

To ask new question, it is best to start another thread. Since this may relate a bit, include a link to it.

I would advise making a new thread as such. Delete your previous post to this thread and mark the thread solved.