dcsimg
Results 1 to 3 of 3

Thread: Return Excel Cell Value Into Text Box

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    5

    Return Excel Cell Value Into Text Box

    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.

  2. #2
    Member
    Join Date
    Apr 2009
    Posts
    34

    Re: Return Excel Cell Value Into Text Box

    try this

    Code:
    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

  3. #3
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,423

    Re: Return Excel Cell Value Into Text Box

    Hi,

    you have to set a Ref. to Excel

    Code:
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width