Return Excel Cell Value Into Text Box

[nadavey21]

Hi, i'm having no look when googling this. When button 1 is pressed I would like it to return the value of cell A1 from a .xlsx file into a textbox. Any help is appreciated.

[pkedpker]

try this

TextBox1.Text = ExcelToTextBox("test.xlsx")

Public Function ExcelToTextBox(filepath As String) As String
                ' string sqlquery= "Select * From [SheetName$] Where YourCondition";
                Dim dt As New DataTable
                Try
                    Dim ds As New DataSet()
                    Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;"""
                    Dim con As New OleDbConnection(constring & "")

                    con.Open()

                    Dim myTableName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")
                    Dim sqlquery As String = String.Format("SELECT * FROM [{0}]", myTableName) ' "Select * From " & myTableName  
                    Dim da As New OleDbDataAdapter(sqlquery, con)
                    da.Fill(ds)  
                    dt = ds.Tables(0)
                   
                    Dim strr as string
                    For Each dataRow As DataRow In dataTable.Rows
                        For Each item In dataRow.ItemArray
                           strr = strr  &  CStr(item)
                         Next
                    Next

                    Return strr
                Catch ex As Exception
                    MsgBox(Err.Description, MsgBoxStyle.Critical)
                    Return ""
                End Try
            End Function

[ChrisE]

Hi,

you have to set a Ref. to Excel

Imports Microsoft.Office.Interop.Excel


 Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim Path As String = "E:\vbExcel.xlsx" 'Datei mit Pfadangabe
        Dim Excel As ApplicationClass = New ApplicationClass
        Dim WorkBook As Workbook = Excel.Workbooks.Open(Path)
        Dim WorkSheets As Sheets = WorkBook.Sheets
        Dim WorkSheet As Worksheet = CType(WorkSheets(1), Microsoft.Office.Interop.Excel.Worksheet)
        Dim cell As String = CStr(WorkSheet.Range("A1").Value)
        TextBox1.Text = cell
        'Dim cell2 As String = CStr(WorkSheet.Range("D10").Formula)
        'TextBox2.Text = cell2
        Excel.Application.Quit()
    End Sub

HTH