Insert new Excel Row from Word

I’m trying to control some aspects of an excel spreadsheet from word.

What I need to do is copy some text from word into certain cells in excel using VBA.  This I can do no problem.  Before I insert the data into the cells I need to create a new row in the spreadsheet which is where I’m getting stuck.

So how do I insert a new row, preferable copying all the formula from the row above by selecting the row and then inserting copied cells.

This would be easy enough from excel as I would just record a macro but even by doing this and trying to get that code to work in word VBA it does not accept it.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
If you post up your code segments ... including the recorded excel macro we can make it work

jdc1944Author Commented:
Theres not much to it at the moment because i have just been playing around with the code to get it to work but as it is...

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open(RecAssessLocation)
Set oWS = oWB.Worksheets("Control Table")

'Insert new row here

'Write to cell
oWS.Cells(2, 2).Value = Objective

Open in new window

Not sure how much use it is to you.  I'm not a beginner at VB but have never had to control excel from word which is where i am sticking.

As for the Macro, ideally i don't want to use one.  I recorded the Macro to see what code it recorded for inserting a new row and then seeing if i could just use this straight into word
Chris BottomleySoftware Quality Lead EngineerCommented:
Specifically was the code for the actions you are recording in excel to implement the required functionality

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

jdc1944Author Commented:
This is what the macro records

    Selection.Insert Shift:=xlDown

Open in new window

Does this get you any closer?
Sub CopyToExcel()
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWs As Excel.Worksheet
    Dim r As Integer

    Set oExcel = CreateObject("Excel.Application")
    Set oWB = oExcel.Workbooks.Open(RecAssessLocation)
    Set oWs = oWB.Worksheets("Control Table")
    r = 1
     With oWs
    .Rows(r + 1).Insert xlDown
        .Rows(r + 1).PasteSpecial xlPasteFormulas
        'Write to cell
        .Cells(r + 1, 2).Value = Objective
    End With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdc1944Author Commented:
Thanks that's spot on.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.