VBA Express : Word - Paste block of cells into an existing table

Word

Paste block of cells into an existing table

Ease of Use

Intermediate

Version tested with

2000, 2003

Submitted by:

mdmackillop

Description:

Incorporates data from a table of cells into a larger table.

Discussion:

You cannot autotext a block of cells into a table as it "nests" into one cell This function inserts a block of data into a table at a selected location. The data is created from previously entered text saved as a separate file, or specially created for the purpose. The function will also add the text from a file if desired.

Code:

instructions for use

			Option Compare Text 
 
Private Sub CommandButton1_Click() 
    If IsNull(ListBox1) Then Exit Sub 
    GetData ListBox1 
    Unload UserForm1 
End Sub 
 
Private Sub UserForm_Initialize() 
    Dim MyDoc As String 
    Dim MyLen As Integer 
     
     
     'Store document name
    TextBox1.Text = ActiveDocument.Name 
     
    MyDoc = ActiveDocument.Name 
    MyLen = Len(MyDoc) - 4 
    MyDoc = Left(MyDoc, MyLen) 
     
     
    For Each tsk In Tasks 
        If Right(tsk.Name, 4) = "Word" Then 
            On Error GoTo skipped 'Avoid error with unsaved open documents
            MyTask = Left(tsk.Name, InStr(tsk.Name, ".doc") - 1) 
            GoTo AddItem 
skipped: 
            MyTask = Left(tsk.Name, InStr(tsk.Name, " - ") - 1) 
AddItem: 
            If MyTask <> MyDoc Then ListBox1.AddItem MyTask 
             
        End If 
    Next 
End Sub 
 
Sub GetData(Location) 
    Windows(Location & ".doc").Activate 
    Selection.HomeKey Unit:=wdStory 
    Selection.EndKey Unit:=wdStory, Extend:=wdExtend 
    Selection.MoveLeft Unit:=wdCharacter, Count:=2, Extend:=wdExtend 
    Selection.Copy 
    Selection.HomeKey Unit:=wdStory 
    Windows(TextBox1).Activate 
    Selection.Paste 
End Sub 
 
 'Code to be saved in a standard module
 
Sub Shows() 
    UserForm1.Show False 
End Sub

How to use:

Create a Userform containing one ListBox. one Textbox (Visible = False) and one CommandButton. Add the above code to the Userform. For general use, the Userform must be saved in the Normal project.
In a standard module, create a Show command for the userform with Modal set to False

Test the code:

Create two documents. The Main document containing a table where the data is to be inserted, the second, a smaller table containing data.
With both/all documents open, run the code from the Main document; select the data document from the ListBox and click on the InsertData button.
Open the four files in the sample; click on the CommandButton in InsertData.doc

Sample File:

InsetData.zip 22.48KB

Approved by mdmackillop

This entry has been viewed 90 times.