CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Transferring, OLE

    Hello, I have a script that will open a word document based on the data I put in row 1 in excel up to column "E" - - -

    Then in "Column F" there is a button that says "Create document" and it triggers Word to open.

    My problem is what about rows 2, 3, 4, and so on and so on...I want that "Create document" button to be listed on all rows in column "F" and when the button is clicked only build a word document based on only the information in the row the button is clicked for
    IE:
    <Tim> <Knows> <Nothing> "<Create Document>"
    <Jim> <Knows> <Nothing> "<Create Document>"

    My code is below...I cant seem to determine what I should add to the script to have a command button and when the command button is clicked only have it open a document based on the row its clicked for (any help is appreciated!!)
    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 = "C:\OLE 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
    Last edited by WizBang; December 4th, 2008 at 05:44 AM. Reason: Added [code] tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured