Using vb.net to add new column in excel

Question

I was hoping someone would be able to offer me some assistance please? My excel worksheet contains various columns, what I was hoping to do is simply just add another column at the end. So if my worksheet contains 20 columns of data i wish to add header to column U1, next time if my worksheet had 22 columns of data I wish to add header to V1 and so on

Now I have managed to get the next column letter however when I try to pass text into the header row I get an error of Object reference not set to an instance of an object on the following line

.Range(ColumnIndexToColumnLetter(lColumn + 1) & 1).Value = "TESTT"

Any help is greatly appreciated, many thanks

    Dim xls As New Excel.Application
    Dim xWorkbook As Excel.Workbook
    Dim xWorksheet As Excel.Worksheet
    Dim lColumn As Long = 0

    xWorkbook = xls.Workbooks.Open("D:\Test.xlsx") 'File Location
    xWorksheet = xWorkbook.Sheets(1)
    xls.Visible = True

    With xWorksheet
        If xls.WorksheetFunction.CountA(.Columns) <> 0 Then
            lColumn = .Columns.Find(What:="*", _
                          After:=.Range("A1"), _
                          LookAt:=Excel.XlLookAt.xlPart, _
                          LookIn:=Excel.XlFindLookIn.xlFormulas, _
                          SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                          SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                          MatchCase:=False).Column
        Else
            lColumn = 1
        End If
    End With

    With xWorksheet
              .Range(ColumnIndexToColumnLetter(lColumn + 1) & 1).Value = "TESTT"
    End With

Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
    Dim div As Integer = colIndex
    Dim colLetter As String = String.Empty
    Dim modnum As Integer = 0

    While div > 0
        modnum = (div - 1) Mod 26
        colLetter = Chr(65 + modnum) & colLetter
        div = CInt((div - modnum) \ 26)
    End While

    Return colLetter
End Function

Wasn't able to reproduce, this code worked for me and added "Test" to "A2". Are you sure that part of the spreadsheet isn't locked or something, or a column isn't hidden? — soohoonigan
– soohoonigan, Commented Jan 10, 2019 at 14:31

patel · Accepted Answer · 2019-01-10 14:41:26Z

1

if you use option strict on you have to use cint for conversion, with this change your code works well

With xWorksheet
  .Range(ColumnIndexToColumnLetter(CInt(lColumn + 1)) & 1).Value = "TESTT"
End With

answered Jan 10, 2019 at 14:41

patel

4401 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using vb.net to add new column in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related