VB Visual Studio Cells error

Asked

Viewed 57 times

0

First of all, thank you for your help. I’m new to code and I couldn’t get an answer to these questions even with searching on the net.

I am trying to develop a code in the visual studio in Vb.net (console) that opens an excel file, reads the used range of the sheet, and identifies the last column used. The idea is to use this last column and loop the row A, all cells to the last column and delete columns that have no content/name.

However Cells is giving me an error 'Cells is not declared. It may not be accessible due to its level Protection'.

Module Module1

    Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    Sub Main()

        excel = New Microsoft.Office.Interop.Excel.Application

        Dim reportPath As String
        reportPath = "excelFile.xlsx"

        wb = excel.Workbooks.Open(reportPath) 'Open the excel the file'
        excel.Visible = True 'makes it visible

        ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activates'
        ws.Activate()

        ws.UsedRange.Select() 'select used range
        Dim lastCol As Integer = ws.UsedRange.Columns.Count
        MsgBox(lastCol)
        ws.Range(Cells(1, 1), Cells(1, lastCol)).Select()



    End Sub

End Module
excel vb.net vba

  • Welcome to Stackoverflow in Portuguese. As the name implies, the Official language used here is English. So, can you Please Translate your Question? If you prefer, you may also Ask this same Question in the English Stackoverflow site.

  • Done. I thought I was in English. Thank you

  • Can validate the question?

  • You say there’s a mistake but you don’t say which.

  • Thanks @ramaral . I think it’s better now.

  • 1

    Won’t be ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Select()?

  • It is. Thank you!

Show 2 more comments

1 answer

0

The correct formatted code is:

Module Module1

    Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    Sub Main()

        excel = New Microsoft.Office.Interop.Excel.Application

        Dim reportPath As String
        reportPath = "excelFile.xlsx"

        wb = excel.Workbooks.Open(reportPath) 'Open the excel the file'
        excel.Visible = True 'makes it visible

        ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activates'
        ws.Activate()

        ws.UsedRange.Select() 'select used range
        Dim lastCol As Integer = ws.UsedRange.Columns.Count
        MsgBox(lastCol)
        ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Select()



    End Sub

End Module

Browser other questions tagged excel vb.net vba

You are not signed in. Login or sign up in order to post.