(VBA to VB.NET code modification) Write value to specific cell to Workbook using VB.NET-CodePudding

I have a code in Excel VBA that finds a specific value in column 2 and writes the value in that found rows column 3:

ThisWorkbook.Worksheets("Sheet1").Cells(Cells.Find("ProjectNumber", lookat:=xlWhole).Row, 3).Value = "TEST"

Now I am trying to get that same function to work while running it from my application I am making in Visual studio and what I've got so far is:

Imports Excel = Microsoft.Office.Interop.Excel
Module Automate_Excel

Public xlApp As Excel.Application = Nothing
Public xlWorkBook As Excel.Workbook = Nothing
Public xlWorkSheet As Excel.Worksheet = Nothing
Sub Excel_FinishProject()

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open("G:\100 Databases\Projects Schedule.xlsx")
    xlApp.Visible = True
    xlWorkSheet = xlWorkBook.Worksheets("sheet1")

'Write
    xlWorksheet("Sheet1").Cells(Cells.Find("ProjectNumber", lookat:=xlWhole).Row, 3).Value = "TEST"

    xlWorkBook.Close()
    xlApp.Quit()
End Sub

End Module

But it doesn't work giving me all kinds of different errors like "xlwhole is not declared" and "cells is not declared" when in my understanding it should be coming from the type library of the excel and code such as:

xlWorkSheet.Cells(2, 5) = "TEST"

which does use "cells"

Could someone help me with the right code?

CodePudding user response：

You need to fully qualify each enumeration. In this case,

Excel.XlLookAt.xlWhole

XlLookAt enumeration (Excel)

In an Excel/VBA environment, those are optional, so you never notice them.

Cells in your code is also not fully qualified. Cells.Find needs a worksheet qualifier. VB.NET does not know what Cells is without a qualifier. Again, in a VBA environment, you do not have to be this explicit, but in VB.NET you do, as there is no "default context"

Your xlWorkSheet variable is not indexed. It's already holding a single reference to xlWorkBook.Worksheets("Sheet1") - So you don't specify the name of it again.

Also, you should store the result of the Find in a Range variable before using it instead of trying to do it all in one line. Then you can check to see if it "didn't find anything" before you try to use the result, and can even see what the result is before taking action

xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
Dim result as Excel.Range = xlWorkSheet.Cells.Find("ProjectNumber", lookat:=Excel.XlLookAt.xlWhole)
If result IsNot Nothing Then
    ' xlWorkSheet.Cells(result.Row, 3).Value = "TEST"

    ' OP says this works instead
    xlWorkSheet.Cells(result.Row, 3) = "TEST"

End IF