We help IT Professionals succeed at work.

Inserting a column in excel sheet from vb.net

I am triying to insert a column into an excel sheet via vb.net.  I've run the macro recorder  to get a start on the code and and am trying to modify the resulting code so that it would work within vb.net.  However I am getting an error on "xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove" The error says that xlToRight and xlFormatFromLeftOrAbove are not declared.
  aWorkSheet.Selection.Insert(Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove)
Watch Question

You need full qualified notation to use excel constants from Vb.Net


  Using x As New ExpExcel
      With x.Wks.Range("A1.L1")
         .Font.Bold = True : .Font.Color = ColorTranslator.ToWin32(Color.Blue)
         .Interior.Color = ColorTranslator.ToWin32(Color.LightCyan)
      End With
      x.Column("A", 13)                               'Trabajo
      x.Column("B", 5)                                'Sup
      x.Column("C:E", 8, , Excel.XlHAlign.xlHAlignRight) 'Fimte,Ffmte,Dur
      x.Column("F", 4)                                'Av
      x.Column("G", 100)                              'Destra

      x.Value("A1", Datos) : Datos = Nothing
  End Using

Imports System.Runtime.InteropServices.Marshal
Friend Class ExpExcel
    Implements IDisposable

    Private Shared WB As Excel.Workbook
    Public Wks As Excel.Worksheet
    Sub New()
            If WB Is Nothing Then
               Dim App = New Excel.Application
               App.SheetsInNewWorkbook = 1
               WB = App.Workbooks.Add
               AddHandler WB.BeforeClose, AddressOf WB_BeforeClose
               App.Visible = True
               App.Caption = Proyecto.Actprj.RevProyecto
               Wks = DirectCast(WB.ActiveSheet, Excel.Worksheet)
               Wks = DirectCast(WB.Worksheets.Add, Excel.Worksheet)
            End If
        Catch ex As Exception
        End Try
    End Sub

    Private disposedValue As Boolean = False        ' Para detectar llamadas redundantes

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: Liberar otro estado (objetos administrados).
            End If
            If Wks IsNot Nothing Then
               With Wks.Application
                    .WindowState = Excel.XlWindowState.xlMaximized
               End With
               Wks = Nothing
            End If
            ' TODO: Liberar su propio estado (objetos no administrados).
            ' TODO: Establecer campos grandes como Null.
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' Visual Basic agregó este código para implementar correctamente el modelo descartable.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' No cambie este código. Coloque el código de limpieza en Dispose (ByVal que se dispone como Boolean).
    End Sub
#End Region

    Public Sub Value(ByVal Pos As String, ByVal Datos(,) As Object)
        Wks.Range(Pos).Resize(Datos.GetLength(0), Datos.GetLength(1)).Value = Datos
    End Sub
    Public Sub Column(ByVal Col As String, ByVal Width As Double, Optional ByVal Format As String = Nothing, Optional ByVal Align As Integer = 0)
        Dim r = DirectCast(Wks.Columns(Col), Excel.Range)
        If Width <> 0 Then r.ColumnWidth = Width
        If Format IsNot Nothing Then r.NumberFormat = Format
        If Align <> 0 Then r.HorizontalAlignment = Align
    End Sub

    Private Shared Sub WB_BeforeClose(ByRef Cancel As Boolean)
        If WB IsNot Nothing Then ReleaseComObject(WB) : WB = Nothing
    End Sub
End Class

Open in new window

Please reply x77 quickly and its answer is close to your need



That was it.  In the bginning of my code I had the following
myExcel = New Excel.Application

When I attempted to use xlright, I had it coded as Excel.xlRight instead of myExcel.xlRight.  That's what was giving me the problem.  I should have caught it but in this project I've been bouncing between T-SQL, vb.net and Excel so I was bound to get confused.  Thanks again.