Multiple Excel Interface questions

[FunkMonkey]

To reduce the number of threads I post regarding similar questions I'm posting them in 1 to hopefully make things less repeated.

This is a conversion from a VBA project.

First question:

I have defined xl as Worksheet, but I get the message that xl is not defined.

        Dim xl as Worksheet

Second Question:
I'm getting "Cells is not declared" when I have tried both Workbook and Worksheet, so I'm not sure why I'm getting this message.

                    xl.Range("C" & CStr(SB_SPIN3_MAIN.Value)).Select
                    szText = xl.Cells(SB_SPIN3_MAIN.Value, 3)

Third Question:
the following code gives me "Property Range is read only" for the range code.

Dim xl As Worksheet
Dim nRow As Integer
'    Dim nListIndex As Integer
Dim nCellCount As Integer

Try

    SelectExcelSheet(szTab)

    nCellCount = Excel.WorksheetFunction.CountA(Excel.Range(szCol & ":" & szCol))

    xl.Range(szCol & nCellCount + 1) = cb.Text

Any input is greatly appreciated.

[Shaggy Hiker]

For the first question, where is that line located?

For the last one, the issue is that you are assigning to a readonly property in this line:

xl.Range(szCol & nCellCount + 1) = cb.Text

If Range is readonly, then it can't be on the left side of the equal sign. Presumably, you are trying to assign to a cell defined by a range, but you need to find a different method. I haven't done that in a very long time. I have some relevant code on a different computer, but don't have that handy. If nobody tells you how to do that by tomorrow, I might be able to look it up.

[jmcilhinney]

For the last one, the issue is that you are assigning to a readonly property in this line:

xl.Range(szCol & nCellCount + 1) = cb.Text

If Range is readonly, then it can't be on the left side of the equal sign. Presumably, you are trying to assign to a cell defined by a range, but you need to find a different method. I haven't done that in a very long time. I have some relevant code on a different computer, but don't have that handy. If nobody tells you how to do that by tomorrow, I might be able to look it up.

I do next to no Office Automation but I have a sneaking suspicion that the range has a Value property that would need to be set in this case. If I wanted to know for sure, I'd search the web for "vb.net set cell value in excel" or the like. There's bound to be plenty of examples of such a common action.

[ChrisE]

here some Basics to open and work with a Excelsheet

Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()

            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\TestFolder\vbexcel.xlsx")

      

            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Sheet1"), Worksheet)

            Dim ColRange = xlSt.UsedRange.Columns.Count 'count the used Columns      
            Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows

            'show UsedRanges
            MessageBox.Show(CStr(ColRange))
            MessageBox.Show(CStr(rowRange))

            With xlSt
                'do stuff here with Sheet
            
                'write Value to Cell
                .Range("D16").Value = 589

                'read value from cell to Textbox
                TextBox1.Text = CStr(.Range("A9").Value)

                'color a Cell
                .Range("D12").Interior.Color = Color.BlueViolet

            End With
            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

   
End Class