Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro-Text from word doc paste to excel empty row
Hi all!
I think that I am very close to the answer on this question so any assistance you could give would be greatly appreciated! I am calling a macro from word to format a page of text then copy and open an excel doc paste it to the first *empty* row in this spreadsheet. So far I have: [more code above...] 'Opens an Excel worksheet located in the default Documents folder strXLPath = "C:\Fill_the_Bank_MasterList.xls" Set objExcelApp = CreateObject("Excel.Application") objExcelApp.Workbooks.Open (strXLPath) Set objExcelBook = objExcelApp.ActiveWorkbook Set objExcelSheets = objExcelBook.Worksheets Set objExcelSheet = objExcelBook.Sheets(1) objExcelSheet.Activate objExcelApp.Application.Visible = True 'Paste clipboard into first empty row ' objExcelSheet.Range("ListTop").Select objExcelSheet.Selection.End(xlDown).Select <--- Error 438 here objExcelSheet.ActiveCell.Offset(1, 0).PasteSpecial End Sub I get an error 438 on this (obj or property not supported on the line as indicated. I can paste these lines into excel and run the macro from inside excel, but when I run from word, I get the error. Should I set this before I run in word? Thanks again! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro-Text from word doc paste to excel empty row
I think you have a couple of problems. Selection belongs to a window or the
application--not the worksheet. Similarly with Activecell. And unless you have a reference to excel in your word project, it won't know what xlDown means. (And if you did have a reference to excel, you wouldn't have used createobject????). objExcelApp.Selection.End(-4121).Select objExcelApp.ActiveCell.Offset(1, 0).PasteSpecial A quick and dirty way to find excel's constants is to open excel, got to the VBE and hit ctrl-G to see the immediate window. then type: ?xldown (? is old basic for print) The immediate window showed this: -4121 (You could also open the object browser and see lots of excel's constants all at once.) J wrote: Hi all! I think that I am very close to the answer on this question so any assistance you could give would be greatly appreciated! I am calling a macro from word to format a page of text then copy and open an excel doc paste it to the first *empty* row in this spreadsheet. So far I have: [more code above...] 'Opens an Excel worksheet located in the default Documents folder strXLPath = "C:\Fill_the_Bank_MasterList.xls" Set objExcelApp = CreateObject("Excel.Application") objExcelApp.Workbooks.Open (strXLPath) Set objExcelBook = objExcelApp.ActiveWorkbook Set objExcelSheets = objExcelBook.Worksheets Set objExcelSheet = objExcelBook.Sheets(1) objExcelSheet.Activate objExcelApp.Application.Visible = True 'Paste clipboard into first empty row ' objExcelSheet.Range("ListTop").Select objExcelSheet.Selection.End(xlDown).Select <--- Error 438 here objExcelSheet.ActiveCell.Offset(1, 0).PasteSpecial End Sub I get an error 438 on this (obj or property not supported on the line as indicated. I can paste these lines into excel and run the macro from inside excel, but when I run from word, I get the error. Should I set this before I run in word? Thanks again! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to cut text out of excel & paste into word? | Excel Worksheet Functions | |||
Special Paste to Next empty row if new data and mark copied Macro | Excel Discussion (Misc queries) | |||
How to set macro to Paste Special Value to next empty column | Excel Worksheet Functions | |||
Using a macro to paste into first empty row in a new worksheet | Excel Worksheet Functions | |||
missing text when using paste special from word to excel | Excel Discussion (Misc queries) |