Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
Visual BASIC in Excel | Excel Worksheet Functions | |||
Make visual basic truely visual! | Excel Programming | |||
Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |