copy from Word to Excel

[Cass]

Hi!
I need a little help.
Select Word text (address 5-6 lines), run macro and paste it in myfile.xls file. http://vbaexpress.com/forum/images/smilies/102.gif

[fumei]

Uh....what have you tried so far?

[Jacob Hilderbrand]

This may work for you.


Option Explicit

Sub WordToExcel()
Dim AppExcel As Object
Dim Wkb As Object
Dim Path As String
'Path to the workbook
Path = ThisDocument.Path & "\Book1.xls"
'Create a new instance of Excel
Set AppExcel = CreateObject("Excel.Application")
'Open the workbook
Set Wkb = AppExcel.Workbooks.Open(FileName:=Path)
'Copy the selected text
Selection.Copy
'Paste into range A1 of the workbook on sheet1
Wkb.Sheets("Sheet1").Select
Wkb.Sheets("Sheet1").Range("A1").Select
AppExcel.ActiveSheet.Paste
'Make the Excel application visible
AppExcel.Visible = True
Set Wkb = Nothing
Set AppExcel = Nothing
End Sub

[Cass]

Uh....what have you tried so far?

i cant open Excel and myfile.xls :think:

Selection and copy/paste is easy

DRJ:
I try this code

[fumei]

Did you try Jake's code?

[Cass]

Hmm i try but now problem to check in Word, is excel file opened or not.
If opened then macro open new myfile.xls as read-only

[fumei]

Huh?

If opened then macro open new myfile.xls as read-only

If it is opened....you want to try and open it again, but as read only?

[Cass]

sry my bad englis but i dont mean open new file. as read-only.
I try explain step-by-step
1. Select text (5-6 rows) in Word
2. run macro ( copy selected text, paste it in myfile.xls)
3. Make new selection in Word and run macro. (myfile.xls is still opened)
4. This new selection paste over earlier pasted selection
5. and so on until workday is over and I close excel file :D

[TonyJollans]

If you want to work with an already open file, then instead of ..


'Create a new instance of Excel
Set AppExcel = CreateObject("Excel.Application")
'Open the workbook
Set Wkb = AppExcel.Workbooks.Open(FileName:=Path)

use ..


Set Wkb = GetObject("Path")
Set AppExcel = Wkb.Parent


If the workbook is not opened, this will open it, but you may find you need to add an extra line to make the rest of the code work ..


Wkb.Windows(1).Visible = True

[Cass]

Thanks!
:friends:
Final code is now this


Sub WordToExcel()
Dim AppExcel As Object
Dim Wkb As Object
Dim Path As String
Dim wbName As String
On Error GoTo Copy_Error
wbName = "Birgit v.2.xls"
Path = "C:HendrikExcelMarko jama"
Set Wkb = GetObject(Path & wbName)
Set AppExcel = Wkb.Parent
'Copy the selected text
Selection.Copy
Wkb.Sheets("Taide").Select
Wkb.Sheets("Taide").Range("C5:C11").Clear
'Paste into range C5 of the workbook on Taide
Wkb.Windows(1).Visible = True
Wkb.Sheets("Taide").Select
Wkb.Sheets("Taide").Range("C5").Select
AppExcel.ActiveSheet.Paste
'Make the Excel application visible
AppExcel.Visible = True
Set Wkb = Nothing
Set AppExcel = Nothing
Exit Sub
Copy_Error:
MsgBox "Viga! Tekst ei ole valitud."
End Sub

[MOS MASTER]

Hi and Welcome to VBAX! :hi:

Glad to see you have it working now...don't forget to mark your thread solved if it has been? :whistle: