Add a row before row 2 in an excel file in vba

[Pat]

I have created a spreadsheet in access vba using DoCmd.TransferSpreadsheet and need to add a row prior to row 2 to insert a heading that describes what the spreadsheet is about.
I have done this in the past but cannot find the code to do it.
I need all the code from declaring code to open the spreadsheet to closing it after use.
Any help is greatly appreciated.

[Pat]

Hey trial and error got me there, this code opens a new row prior to row 1 then puts in some wording:

Code: Select all

    Dim xlapp As Object
    Set xlapp = New Excel.Application
    
    Dim oWB As Excel.Workbook, mySh As Object
    
    xlapp.Visible = True
''    Set oWB = xlapp.Workbooks.Open(sPathFn)

''    oWB.Range("2:1").Insert
    '   set up the heading
    Set mySh = xlapp.Workbooks.Open(sPathFn).Sheets(1)
    
    mySh.Rows(1).Insert
    mySh.Cells(1, 1) = "This is a heding for Evonne"
    'mySheet.Application.activeworkbook.Save
    mySh.Parent.Close SaveChanges:=True

''    Set oWB = Nothing
    Set mySh = Nothing
    Set xlapp = Nothing

[HansV]

I'd still use the oWB object:

Code: Select all

    Dim xlapp As Object
    Dim oWB As Excel.Workbook
    Dim mySh As Object
    Set xlapp = New Excel.Application
    xlapp.Visible = True
    Set oWB = xlapp.Workbooks.Open(sPathFn)
    Set mySh = oWB.Sheets(1)
    mySh.Rows(1).Insert
    mySh.Cells(1, 1) = "This is a heading for Evonne"
    oWB.Close SaveChanges:=True
    Set oWB = Nothing
    Set mySh = Nothing
    Set xlapp = Nothing

Apart from that, I'd prefer to use more consistent names for the variables, and to declare them all either as Object or as Excel.something, not a mixture.

For example, using early binding (requiring a reference to the Microsoft Excel n.0 Object Library):

Code: Select all

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Set xlApp = New Excel.Application

or using late binding (not requiring a reference to the Excel library):

Code: Select all

    Dim objApp As Object
    Dim objWbk As Object
    Dim objWsh As Object
    Set objApp = CreateObject("Excel.Application")

[Pat]

I quite agree about the naming convention but i was just trying to get it working, got a bit from here and a bit from there.
I decided to go with late binding.
Thanks for all that, it helps a lot in terms of understanding it as well as getting it to work.