Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extract Excel data to Word Table with VBA

Status
Not open for further replies.

gtaborda

Programmer
May 13, 2007
75
GB
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
 
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


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top