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.
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.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.