Using VB and Excel

[lesthan60]

When I click text1, readwritetest.xls then open readwritetest.xls (read only) cell A1 has not changed. After exiting VB readwritetest.xls remains as read only.

Private Sub cmdOpenWorkSheet_Click()
Dim xls As Excel.Application
Dim xlBook As Excel.Workbook
Set xls = New Excel.Application
Set xlBook = xls.Workbooks.Open(App.Path & "\readwritetest.xls")
xlBook.Sheets("sheets").Cells(1, 1) = Me.Text1
End Sub
 
Private Sub Text1_Click()
Call cmdOpenWorkSheet_Click
End Sub

[RobDog888]

Why are you opening the file that is read only if you want to make a change to it?

If the workbook is marked as readonly you can save a copy of it as a different workbook name using the .SaveAs method.

[lesthan60]

These are the steps I took:
The file was not open.
I ran the code.
I tried to open the file to verify an update had occured.
File was locked for editing.
Opened the file as read only.
No update had occured.
Exited VBE.
I tried to open the file.
File was locked for editing.

I just commented that when I tried to open the file after I had run the code - to verify that an update had occured, it would only open as read only.
I also noted that after I had exited VBE, the file was still "lock for editing".

[RobDog888]

You need to close VB6 and the Excel file and check in the task manager for any other instances of Excel. If you find any and dont have any other workbooks open, then terminate them. Then doubleclick on your excel file and see if it opens as read/write. Also, make sure the file is not marked as read only in the file properties when you right click on the file in Explorer.

[DKenny]

'.....
Set xls = New Excel.Application
Set xlBook = xls.Workbooks.Open(App.Path & "\readwritetest.xls")
xlBook.Sheets("sheets").Cells(1, 1) = Me.Text1
End Sub

You are creating a 2nd instance of excel, in the background and opening the file in that instance. You never close the file and you never remove that instance of excel. This is why the file is read-only, you still have it open in the other instance.

You need to add code to
1/ Close xlBook, svaing your changes.
2/ Set xls = nothing.

[lesthan60]

Thanks for your replies.
RobDog888:There were several instances of Excel running. I closed all of them and the file opened normally.

DKenny: What's wrong with this, after clicking text1, runtime error '9' appears and the underlined code is highlighted.

Private Sub cmdOpenWorkSheet_Click()
Dim xls As Excel.Application
         Dim xlBook As Excel.Workbook
          Set xls = New Excel.Application
         Set xlBook = xls.Workbooks.Open(App.Path & "\readwritetest.xls")
      [U] xlBook.Sheets("sheets").Cells(1, 1) = Me.Text1[/U]
      xlBook.Save
        Set xls = Nothing
End Sub
Private Sub Text1_Click()
    Call cmdOpenWorkSheet_Click
End Sub

[DKenny]

What is the value of Me.text1?

Also you need to clear the xlBook object variable before you clear the xls object variable.

xlBook.Save
Set xlBook = nothing  'add this line
Set xls = Nothing
End Sub

[lesthan60]

Thanks DKenny but why does runtime error '9' appear? Have I coded that line correctly? text1.value = "test"

[DKenny]

This might seems like an obvoious question, but... Does "readwritetest.xls" have a worksheet called "sheets"?

[lesthan60]

lol, thanks DK - I can read and write to that file from VB.

[lesthan60]

DKenny, I still can't close the file. Can you see why?

Private Sub cmdOpenWorkSheet_Click()
Dim xls As excel.Application
         Dim xlBook As excel.Workbook
          Set xls = New excel.Application
         Set xlBook = xls.Workbooks.Open(App.Path & "\readwritetest.xls")
       xlBook.Sheets("sheet1").Cells(1, 1) = Me.Text1
       Me.Text2 = xlBook.Sheets("sheet1").Cells(2, 1)
 
       xlBook.Save
        Set xlBook = Nothing
        Set xls = Nothing
 
End Sub
 
Private Sub Text1_DblClick()
    Call cmdOpenWorkSheet_Click
End Sub

[DKenny]

Try replacing

xlBook.Save
        Set xlBook = Nothing
        Set xls = Nothing

with

Application.DisplayAlerts = False
xlBook.Close SaveChanges:=True
Application.DisplayAlerts = True
Set xlBook = Nothing
Set xls = Nothing

[lesthan60]

That seems to have taken care of it, thanks.