Click here to Skip to main content
14,330,512 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi. I have an excel file that contains 3 sheets that in every sheet in column A a specific data will be written by other program.
I just want to create a Form in Vb.net that contains 3 textbox and a button that by pressing the button the program read the last filled cell in column A from every sheet and write it in the textbox that belongs to specific sheet

What I have tried:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim oExcelApp As New Excel.Application
        Dim oExcelBook As Excel.Workbook
        Dim oExcelSheet As Excel.Worksheet
        Dim sheetNumber As Integer = 1 '1-based array
        Dim oData As Object = Nothing
        Dim fileNameAndPath As String
        fileNameAndPath = textbox1.Text
        Try
            oExcelBook = oExcelApp.Workbooks.Open(fileNameAndPath)
            oExcelSheet = CType(oExcelBook.Worksheets("tag1"), Excel.Worksheet)
            'Read data
            Dim excelRange As String = column(1) & rowIndex.ToString()
            oData = oExcelSheet.Range(excelRange).Value
        Catch exp As COMException
            MessageBox.Show(exp.Message)
        Catch exp As Exception
            MessageBox.Show(exp.Message)
        End Try
        Return oData
    End Sub
End Class
Posted
Updated 7-Oct-19 10:47am
Comments
F-ES Sitecore 7-Oct-19 11:49am
   
What's wrong with the code you have so far?
sepehrtavangar 7-Oct-19 15:00pm
   
this code does not work at all

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
this code does not work at all


Well, it would partially work, but i needs few minor changes...

1. Procedure does not return value.
It returns control to the calling code.
See:
Sub Procedures (Visual Basic) | Microsoft Docs[^]
Procedures in Visual Basic | Microsoft Docs[^]

2. You have declared and initialized oData variable, but you did not return its value to any controls on the form.

Please, read comments in below code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'create and initialize Excel instance
    Dim oExcelApp As New Excel.Application
    'define variable - type of Workbook
    Dim oExcelBook As Excel.Workbook
    'define variable - type of Worksheet
    Dim oExcelSheet As Excel.Worksheet
    Dim sheetNumber As Integer = 1 '1-based array
    Dim sData As String = ""
    Dim fileNameAndPath As String = textbox1.Text
    Try
        'open Workbook
        oExcelBook = oExcelApp.Workbooks.Open(fileNameAndPath)
        'get Workshhet
        oExcelSheet = CType(oExcelBook.Worksheets("tag1"), Excel.Worksheet)
        'get cell name
        Dim excelRange As String = "A1"
        'read data from A1 cell to string variable
        sData = oExcelSheet.Range(excelRange).Value.ToString()
        'put data to TextBox
        Me.TextBox2.Text = sData

        'NOTE:
        'you should close workbook and Excel application here!

    Catch exp As COMException
        MessageBox.Show(exp.Message)
    Catch exp As Exception
        MessageBox.Show(exp.Message)
    End Try
End Sub
   
v2
Comments
sepehrtavangar 7-Oct-19 18:15pm
   
Hi thanks a lot for your wonderful help my problem is almost solved but there is a little problem that here we read the value of A1 cell in excel worksheet but i need the value of last Filled cell in column A for exapmle it could be A17 another time A33
Maciej Los 8-Oct-19 2:17am
   
So, you have to use Dim oExcelRange As Excel.Range = oExcelSheet.Range("A" & oExcelSheet.Rows.Count).End(xlUp) to find last not empty cell. Then: sData = oExcelRange.Value.ToString()
Good luck!
sepehrtavangar 8-Oct-19 3:03am
   
Thanks A Lot for your wonderful help.
Maciej Los 8-Oct-19 7:06am
   
You're very welcome.
If your issue has been resolved, please accept my answer (green button).
sepehrtavangar 8-Oct-19 7:09am
   
just done,
Maciej Los 8-Oct-19 7:12am
   
Thank you.
Cheers!
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100