Add row in excel, shift colums down

[thomaspender]

is it possible to add a row in excel and shirt all other rows down with
vbscript?

[McKirahan]

"thomaspender" <gr...@discussions.microsoft.com> wrote in message
news:7BC7C9FC-AE9D-4517...@microsoft.com...

> is it possible to add a row in excel and shirt all other rows down with
> vbscript?

Recording a macro of the action then viewing it revealed this code:

Range("A3").Select
Selection.EntireRow.Insert

Incorporate it into your script.which uses
CreateObject("Excel.Application").

What do you have so far?

[urkec]

"McKirahan" wrote:

> "thomaspender" <gr...@discussions.microsoft.com> wrote in message
> news:7BC7C9FC-AE9D-4517...@microsoft.com...
> > is it possible to add a row in excel and shirt all other rows down with
> > vbscript?
>
> Recording a macro of the action then viewing it revealed this code:

This is what I came up with after using the Macro Recorder:

Const xlDown = -4121

Set objExcel = Createobject ("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True

Set objWBook = objExcel.Workbooks.Open ("C:\test.xls")

intRow = 1
strRow = CStr(intRow & ":" & intRow)
objWBook.Sheets(1).Rows(strRow).Insert xlDown

--
urkec

[Richard Mueller [MVP]]

thomaspender wrote:

> is it possible to add a row in excel and shirt all other rows down with
> vbscript?

There may be several ways to do this, but the following worked for me:
==========
Option Explicit
Dim objExcel, strExcelPath, objSheet

strExcelPath = "c:\Scripts\Example.xls"

' Open specified spreadsheet and select the first worksheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Modify a cell.
objSheet.Cells(3, 2).Value = "Test"

' Insert a row before row 5.
objSheet.Range("B5").EntireRow.Insert

' Label the new rows. Row 4 is unchanged,
' but what was row 5 is now row 6.
objSheet.Cells(4, 2).Value = "Row 4"
objSheet.Cells(5, 2).Value = "Row 5"
objSheet.Cells(6, 2).Value = "Row 6"

' Save and quit.
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--