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.
Add a row before row 2 in an excel file in vba
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Add a row before row 2 in an excel file in vba
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row before row 2 in an excel file in vba
I'd still use the oWB object:
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):
or using late binding (not requiring a reference to the Excel library):
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
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
Code: Select all
Dim objApp As Object
Dim objWbk As Object
Dim objWsh As Object
Set objApp = CreateObject("Excel.Application")
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Add a row before row 2 in an excel file in vba
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.
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.