Visual basic linkup with Excel

[Akash]

I created a form in Visual Basic

In that form i have two Text Box (Name & Address) and a Command Button.


Now i want that whenever the user enters the details in the Text Box it
should update the Excel File.

i am using the following codes

Private Sub cmdOk_Click()
Dim object1 As Excel.Application
Set object1 = CreateObject("excel.application")
object1.Workbooks.Open App.Path & "\TEST.xls"
Set object3 = object1.ActiveSheet
object1.Visible = True

object1.ActiveSheet.Cells(1, 1).Value = Text1.Text
object1.ActiveSheet.Cells(1, 2).Value = Text2.Text

'object1.ActiveWorkbook.Save
'object1.ActiveWorkbook.Close
'object1.Quit
End Sub


but everytime when i am clicking on the Save Button its opening the new
workbook with read only options.

I want that it should update the same excel present in the folder
everytime and in new row.

moreover i want that no one shuld leave the empty text box. If left
then it shoud not allow it to save it.

How to do it.

Pls help.

Regards

Akash

 

[Chip Pearson]

Rather then creating and destroying Excel every time the button is clicked
(horribly inefficient), create a Public variable in a standard code module,
and use code like

Public XL As Excel.Application

Private Sub cmdOk_Click()
On Error Resume Next
If XL Is Nothing Then
''''''''''''''''''''''''''''''''
' Get a reference to an existing
' instance of Excel.
''''''''''''''''''''''''''''''''
Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then
'''''''''''''''''''''''''''
' No running instance,
' create a new one.
'''''''''''''''''''''''''''
Set XL = CreateObject("Excel.Application")
If XL Is Nothing Then
MsgBox "Cannot Find Or Create Excel Application"
Exit Sub
End If
End If
End If
On Error GoTo 0
XL.Workbooks.Open(App.Path & "\Test.xls")
'
' and the rest of your code.
'

Then in your shutdown code, use code like

Dim WB As Excel.Workbook
For Each In XL.Workbooks
WB.Close SaveChanges:=True 'or False
Next WB
XL.Quit

And I hope that naming variables "object1", "object2" and so on is just for
testing. In the real world, you'll want meaningful variable names.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Jon Peltier]

XL.Workbooks.Open(App.Path & "\Test.xls")

Also, if Excel is already open, check whether the workbook is already open,
before reopening it. My first thought was that you were reopening the same
file in a new instance of Excel, which would make it read only.

- Jon