Visual basic linkup with Excel
 

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

Visual basic linkup with Excel
 

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)


"Akash" wrote in message
ups.com...
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

Visual basic linkup with Excel
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Chip Pearson" wrote in message
...
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)


"Akash" wrote in message
ups.com...
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