Extract Excel data to Word Table with VBA

[gtaborda]

hello there

I have an Excel sheet and Word Document open at the same time.

I need to export certain data from the excel sheet to a certain "cell" on a table found in Word.

The excel is opened originally from Word (I have a macro/button which does that).

I select certain data from Excel, then I would like to 'press' a CELL in Excel (using Worksheet_SelectionChange) and this will cause a number of cells data in Excel to be moved to a certain row in a table on Word.

Any ideas how to do the actual transfer from one to the other with VBA ????

Thanks in advance


Any ideas

 

[JensKKK]

Nice

I am doing the same, but I transfer data from Excel to MS Word. Copy and Paste of test is quiet straight forward. Graphics gives me more headaches.

Open MS word from excel
Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim fleNme As String
Dim Fldnme As String
Dim MyPath2 As String
Dim Mypath As String
Dim wordRange As Word.Range
Dim Var, Var2

Application.StatusBar = "Copy Detailed Results Table: "
Application.ScreenUpdating = False
'fleNme = "Selectivity Report"
'Fldnme = "C:\Documents and Settings\jkoopmann\My Documents"
'Mypath = Fldnme
'MyPath2 = Mypath & "\" & fleNme & ".doc"
On Error Resume Next
Var2 = 1
Set WordObj = GetObject(, "Word.Application")
If err <> 0 Then
msg = MsgBox("1 test Error " & err)
Set WordObj = CreateObject("Word.Application")
err.Clear
End If

Var = WordObj.Windows.Count
'msg = MsgBox("windows count " & Var)
With WordObj
.Documents.Add DocumentType:=wdNewBlankDocument
.ActiveDocument.ShowSpellingErrors = False
.ActiveDocument.LineNumbering.Active = False
.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
End With
WordObj.Visible = True
Var2 = Var + 1
'Application.StatusBar = "Opening MS Word"
WordObj.Windows(Var2).Activate

Application.StatusBar = "Transfering Information into MS Word"
'Now it is possible to copy paste our data into MS Word

---->>> selecting, copy and paste data to MS Word.

Application.StatusBar = "Writing table into memory " & i
dummy = Sheets("3d rotate").Cells(8999 + i, 1)
Sheets("detailed results").Cells(4 + i + j, 2) = "Results for compound " & dummy 'read write compound name
Sheets("detailed results").Cells(5 + i + j, 2) = "Hit"
Sheets("detailed results").Cells(5 + i + j, 5) = "% Inhibition"
Sheets("detailed results").Cells(5 + i + j, 7) = "T-Test (1-P value)"
Sheets("detailed results").Cells(5 + i + j, 10) = "dRFU"
Sheets("detailed results").Select
Sheets("detailed results").Cells(4 + i + j, 2).Resize(2, 9).Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Sheets("detailed results").Cells(4 + i + j, 2).Resize(1, 5).Select
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
'Selection.Copy
With WordObj
' .Selection.Paste
End With