CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    8

    how to display an excel cell value into a text box

    does any know how to read a data in a an excel cell then display it in a text box..please help me
    Last edited by wankhusairi; September 25th, 2007 at 08:49 PM.

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    Re: how to display an excel cell value into a text box

    The code is totally self explanatory, In the load event we're going to open the new instance of the excel library and our excel file “book1.xls” will be accessible from our code. Then we'll use Command1 to retrieve data from book1, please note that you must have some data in the excel file. Similarly Command2 is used to put/replace the data in the excel sheet cells.

    'do declare these variables you need to add a reference'to the microsoft excel 'xx' object library.
    'you need two text boxes and two command buttons'on the form, an excel file in c:\book1.xls
    Code:
    Dim xl As New Excel.Application
    Dim xlsheet As Excel.Worksheet
    Dim xlwbook As Excel.Workbook 
    
    Private Sub Command1_Click()
    'the benifit of placing numbers in (row, col) is that you'can loop through different directions if required. I could'have used column names like "A1" 'etc. 
        Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1    
        Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2 
    'don't forget to do this or you'll not be able to open'book1.xls again, untill you restart you pc.    
        xl.ActiveWorkbook.Close False, "c:\book1.xls"
        xl.Quit
    End Sub 
    
    Private Sub Command2_Click()
        xlsheet.Cells(2, 1) = Text1.Text
        xlsheet.Cells(2, 2) = Text2.Text
        xlwbook.Save 
    
    'don't forget to do this or you'll not be able to open'book1.xls again, untill you restart you pc.    
        xl.ActiveWorkbook.Close False, "c:\book1.xls"
        xl.Quit
    End Sub 
    
    Private Sub Form_Load()
        Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
        Set xlsheet = xlwbook.Sheets.Item(1)
    End Sub 
    
    Private Sub Form_Unload(Cancel As Integer)
        Set xlwbook = Nothing
        Set xl = Nothing
    End Sub
    Taken from Developer Fusion
    Author MahR
    Last edited by jggtz; September 27th, 2007 at 02:01 PM.

  3. #3
    Join Date
    Sep 2007
    Posts
    8

    Re: how to display an excel cell value into a text box

    i forgot to mention... if i have save my data in many books.xls.. and the row and column i have set the same...is it the way to way to display the cell value onto a textbox...

    i also have another problem.. i have created a macro using micsoft excel 2003.. i want to display it on a mschart that i have name it mygraph.. when i click on the plot button an error occured.. it says that run_time error'1004'
    method' range of object_global failed' and then it show an yellow arrow at the Range("A1:D26").Select . i dont know what to do and i hope you can guide me..
    .. here i attached my code.. pls tell me what i have done wrong.

    Private Sub CmdPlot_Click()
    With GUI.MyGraph
    Range("A1:D26").Select
    Application.CutCopyMode = False
    Range("A3:D26").Select
    Selection.Cut
    Range("A3:D26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A2").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("A26:D26").Select
    Selection.ClearContents
    Range("A1:D25").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D25"), PlotBy _
    :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Api Index"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PPM"
    End With
    End With
    End Sub
    Last edited by wankhusairi; September 27th, 2007 at 01:47 AM.

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: how to display an excel cell value into a text box

    First, use CODE tags, and/or eliminate smilies (checkbox) in your post. We can't read that.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Sep 2007
    Posts
    8

    Re: how to display an excel cell value into a text box

    i have tried your code is say that data error or method not found.. an it highlited at Text1.text. i have attached what have had haapen to my program
    Attached Files Attached Files
    Last edited by wankhusairi; September 27th, 2007 at 03:56 AM.

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: how to display an excel cell value into a text box

    You need to place 5 textboxes on the form, as he calls them all.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Sep 2007
    Posts
    8

    Re: how to display an excel cell value into a text box

    i have put 5 boxes in my form but when i run it it said compile error the variable not define. how should i define it..

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured