Exporting data from MS Excel to MS Word VBA

[andywsw]

Hi guys i need advice on VBA ( ms word )

Let me explain what i am trying to do is that , firstly i have done
userform in MS words. In one of my frame there is this label, when th
user clicks on it , it will put data from textbox1 to Excel workboo
named "book1.xlsx" and split the words by " ; " . This is done becaus
the text box has multple value in a string which paste into a cell o
excel which then split into words to row of cells in column A
Resulting like a normal table with datas in excel.


right now what i need help is that i would like to copy down to th
*_last_row_of_data_* ( column A to G ) and paste the rage of data int
my words by *_DataType:=wdPasteOLEObject__*on to a book mark cal
"excel table"



This is my code i have writen so far
Private Sub Label17_Click()
Application.ScreenUpdating = False
Set objexcel = CreateObject("excel.Application")
Set wb = objexcel.Workbooks.Open("C:\Desktop\book1.xlsx")


wb.Worksheets("Sheet1").Range("A2") = TextBox1.Value


Dim sText As String, arText

sText = wb.Worksheets("Sheet1").Range("A2").Value

arText = Split(sText, ";")

wb.Worksheets("Sheet1").Range("A2:A100" & CStr(16
UBound(arText))).Value
wb.Worksheets("Sheet1").WorksheetFunction.Transpose(arText)

Application.ScreenUpdating = True
end sub


Thanks in advance ! cheer