Trying to move a row in Excel

[itmasterw]

Hi,
I am tring to insert a row or shift all the row down one; depending how you want ot look at it. In oter words I have data in rows one through 10 and I want to move it down so that I can put a headings in row one.
In Excel VBA I would do something like:
Rows("1:1").Select
Selection.Insert Shift:=xlDown
But you can not use exactly this code in VB.Net and I cannot figure out how to do it here.
Here is code that I used, succsessfuly to widing a cell:
rng = xlsSheet.Application.Range("D")
rng.ColumnWidth = 17.0
I need something like this to insert a row?
can someone help me pleas I am really stuck on this.
Thank you

 

[JohnH]

It's almost the same code with VB.Net, but remember in VBA you're operating from inside the Excel application, in VB.Net you're operating from outside the Excel application. See this article and other for how to connect to the automation server: How to automate Microsoft Excel from Visual Basic .NET

 

[itmasterw]

Reply

Unfortuantly, unless I missed it that site did not have naything on this. And I thought too that it would be close but nothing I have found or tryed has worked. Do you have a sample of code that works or know where I can get it?
Thank you

 

[JohnH]

How far is your code now? Have you connected to the automation server, ie you have the reference to the application object? Have you opened a workbook? When you say "Rows("1:1").Select" do you mean the row of the active sheet? You can get the active sheet from the ActiveSheet property of the application or book, the first lines of the sample show how to do this. Now you can do this:

sheet.Rows("1:1").Select()
app.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)

Notice that instead of Rows().Select from outside you have to say what Rows is, ie Rows of the given worksheet. And instead of Selection you have to say which application the selection is of. The xlShiftDown constant values is now not defined from inside the Excel application, but instead they are defined by the interop automation libraries.

When you are inside the living room you can say "close the door" to have someone close the living room door, when you are not in the living room you have to say "close the living room door" to achieve same effect. Can you see how this relates to being inside or outside Excel?

 

[itmasterw]

Reply

Thanks that worked, but if I could as one more question my work is not saving fully, it does not error but it does not save everythign; and I have a save in ther (at the end of mt try block). It is nto saving the formating of the leadding zeros, the colors, or the 12 digit length I am given one of the fields. It works because it does all this when I step through it but then in the end it is gone; but the headings are there so it is saving something. Do you see what I am doing wrong?
Thank you
Here is my whole code:
Option Explicit On
Imports System
Imports System.Drawing
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Office.Interop
Imports System.IO

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Create and save the Excel sheet

Dim xlsApp As New Excel.Application
Dim xlsWB As Excel.Workbook = Nothing
Dim xlsSheet As New Excel.Worksheet

Dim rng As Excel.Range

xlsApp = New Excel.Application

Try

xlsApp.Visible = True
xlsWB = xlsApp.Workbooks.Open("\\gld\GM\OE\Delta.xls")
xlsSheet = xlsWB.Worksheets.Item(1)

xlsSheet.Rows("1:1").Select()
xlsApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)

rng = xlsSheet.Application.Range("A1")
rng.Value = "GRP_NBR"
rng = xlsSheet.Application.Range("B1")
rng.Value = "SUB_GRP"
rng = xlsSheet.Application.Range("C1")
rng.Value = "PKG_NBR"
rng = xlsSheet.Application.Range("D1")
rng.Value = "GRPKEY"
rng = xlsSheet.Application.Range("E1")
rng.Value = "PKG_EFF_DATE"
rng = xlsSheet.Application.Range("F1")
rng.Value = "PKG_TERM_DATE"
rng = xlsSheet.Application.Range("G1")
rng.Value = "PLASM_NBR"
rng = xlsSheet.Application.Range("H1")
rng.Value = "UC||LOB||COR||RP"
rng = xlsSheet.Application.Range("I1")
rng.Value = "PLASM_CAT_CODE"
rng = xlsSheet.Application.Range("J1")
rng.Value = "PLASM_DESC"
rng = xlsSheet.Application.Range("K1")
rng.Value = "GRP_NAME"
rng = xlsSheet.Application.Range("L1")
rng.Value = "BASE w/Inpatient"
rng = xlsSheet.Application.Range("M1")
rng.Value = "OP/ER/AMB"
rng = xlsSheet.Application.Range("N1")
rng.Value = "PCP/Specialist"
rng = xlsSheet.Application.Range("O1")
rng.Value = "Coinsurance"
rng = xlsSheet.Application.Range("P1")
rng.Value = "OOP Max"
rng = xlsSheet.Application.Range("Q1")
rng.Value = "Waivered/ Non-Waivered"
rng = xlsSheet.Application.Range("R1")
rng.Value = "MRP"
rng = xlsSheet.Application.Range("S1")
rng.Value = "MRP Name"
rng = xlsSheet.Application.Range("T1")
rng.Value = "StepWise Subgroup"
rng = xlsSheet.Application.Range("U1")
rng.Value = "StepWise Subgroup Name"
rng = xlsSheet.Application.Range("V1")
rng.Value = "Population"
rng = xlsSheet.Application.Range("W1")
rng.Value = "Population Name"
rng = xlsSheet.Application.Range("X1")
rng.Value = "Change"

'xlsSheet = xlsWB.ActiveSheet()
rng = xlsSheet.Application.Range("B:B")
rng.Cells.NumberFormat = "000"
rng = xlsSheet.Application.Range("D")
rng.Cells.NumberFormat = "############"

rng = xlsSheet.Application.Range("R:R")
rng.Cells.Interior.ColorIndex = 6 '6 = the color Yellow
rng = xlsSheet.Application.Range("S:S")
rng.Cells.Interior.ColorIndex = 6
rng = xlsSheet.Application.Range("T:T")
rng.Cells.Interior.ColorIndex = 6
rng = xlsSheet.Application.Range("U:U")
rng.Cells.Interior.ColorIndex = 6
rng = xlsSheet.Application.Range("V:V")
rng.Cells.Interior.ColorIndex = 6
rng = xlsSheet.Application.Range("W:W")
rng.Cells.Interior.ColorIndex = 6

rng = xlsSheet.Application.Range("D")
rng.ColumnWidth = 17.0

rng = xlsSheet.Application.Range("F:F")
rng.ColumnWidth = 17.0


xlsWB.Save()

Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())

Finally

''Close the worksheet
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet)
xlsSheet = Nothing

'Close the workbook
If xlsWB IsNot Nothing Then
xlsWB.Close(False, "", Nothing)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB)
xlsWB = Nothing
End If

'Close Excel
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
xlsApp = Nothing
GC.Collect()

End 'Close the program
End Try

End Sub
End Class