Inserting Excel Rows via VBScript

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Inserting Excel Rows via VBScript

Logged in as: Guest

arrSession:exec spGetSession 2,2,88

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Inserting Excel Rows via VBScript
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Inserting Excel Rows via VBScript - 6/2/2001 12:35:13 AM

jennyd

Posts: 6
Score: 0
Joined: 6/1/2001
From: USA
Status: offline

I am going to give this one more try.
I am having some difficulty with determining the line of code that will
allow me to insert a new row above the currently selected cell. The
code created when you record a macro and do this manually will not
work when transported to VBScript.

Here is a snippet of code, with the offending line bolded in red.

Set oEx =
GetObject("","Excel.Application")
oEx.Visible = true
oEx.Workbooks.open "C:\test.xls"
Set oWS =
oEx.ActiveWorkbook.Sheets("Sheet1")
oWS.Activate
Toprow = 1
Bottomrow = 8

For i = Toprow to Bottomrow
oWS.Range("a1").Offset(i,0).Select
If oWS.Range("a1").Offset(i+1,0) <> ""
Then

oWS.Selection.EntireRow.Insert

oWS.Range("a1").Offset(i,0).Select
End If
oWS.Range("a1").Offset(i,0) = "Stuff"
& i
oWS.Range("a1").Offset(i,1) = "More
Stuff" & i
Next

Try this on a simple sheet with any data in cell A1 and in A5 and the
rest empty.

Desperately wanting an approach!!!

Post #: 1

Re: Inserting Excel Rows via VBScript - 6/2/2001 12:36:42 AM

countryfun

Posts: 12
Score: 0
Joined: 5/31/2001
From: USA
Status: offline

Hi

This code should work for you

Dim oEX 'As Excel.Application
Dim oWB 'As Excel.Workbook
Dim oWS 'As Excel.Worksheet

Dim lTopRow 'As Long
Dim lBottomRow 'As Long
Dim iIndex 'As Integer

Set oEX =
CreateObject("Excel.Application")
Set oWB =
oEX.Workbooks.Open("d:\book3.xls")
Set oWS = oWB.Sheets("Sheet1")

oEX.Visible = True
oWS.Activate
lTopRow = 1
lBottomRow = 8

For iIndex = lTopRow To lBottomRow
oWS.Range("A1").Offset(iIndex + 1,
0).Select

If oWS.Range("A1").Offset(iIndex +
1, 0) <> "" Then
With oEX.Selection
.EntireRow.Insert
End With
End If
Next

If Not oWB Is Nothing Then
With oWB
.Save
.Close
End With
End If

Set oWB = Nothing
Set oWS = Nothing

If Not oEX Is Nothing Then
oEX.Quit
End If

Set oEX = Nothing

(in reply to jennyd)

Post #: 2

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Inserting Excel Rows via VBScript

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts