0

Apologies, this is probably a very simple problem, due to me stumbling through basic without any tuition....

I am trying to display cells from an excel file in labels attached to a table. I can manipulate the excel file OK and I can do it if I use text boxes.

For example, using text boxes (this is an extract):

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel   

Public Class Form1

    Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook
    Dim excelfilepath As String = AppDomain.CurrentDomain.BaseDirectory
    Dim n As Integer = 0

    Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        workbook = APP.Workbooks.Open(excelfilepath & "mydata.xlsx")
        worksheet = workbook.Worksheets("sheet1")
        APP.Visible = False

        For n = 1 To 10
            Me.Controls("textbox" & n).Text = DirectCast(worksheet.Cells(1, n),   Excel.Range).Value.ToString
        Next
    End Sub
End Class

The above works fine. However, if I substitute the textbox line in the for/next loop with:

Me.Controls("label" & n).Text = DirectCast(worksheet.Cells(1, n - 1), Excel.Range).Value.ToString

I get an unhandled exception "object reference not set to an instance of an object"

Writing to a single label, thus:

Label16.Text = DirectCast(worksheet.Cells(1, n - 1), Excel.Range).Value.ToString

works just fine.

What am I doing wrong?

7
  • 5
    For textboxes you are using Cells(1, n) but for labels Cells(1, n - 1). Also, your single label test uses label # 16, while your n goes from 1 to 10. Check if you really have all the labels between label1 and label10. Commented Dec 5, 2023 at 14:36
  • Visually, a TextBox with .BorderStyle set to None and .ReadOnly set to True is the same as a Label,* and it offers the user the ability to copy-n-paste the data. (*Same visually, but you'd have to handle sizing because the TextBox doesn't have the .AutoSize property.) Commented Dec 5, 2023 at 16:47
  • Yes, sorry about the confusion, there. I'd used different numbers for labels and boxes as that's how they appear on the form. My point is, if I substitute the word "Label" for "Textbox", one works, the other fails. Should the command be exactly the same? In other words, is it just a dodgy bit of maths I'm using to calculate the value of n. Commented Dec 5, 2023 at 18:08
  • I've since checked my maths by using a msgbox and it's ok! My command still fails using an expression, but an absolute reference, e.g 'Label9.Text' will work. Commented Dec 5, 2023 at 18:19
  • 1
    Your labels in your form should be named label1 to label10, because that's what's in your loop. Are these labels contained in another container, a panel, a groupbox? If so, you should use <Name of Container>.Controls("label" & n) instead of Me.Controls("label" & n). Also, excel rows and columns indexing start from 1 not 0. So this will produce an error DirectCast(worksheet.Cells(1, n - 1), Excel.Range).Value.ToString if n is 1 Commented Dec 6, 2023 at 0:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.