Results 1 to 12 of 12

Thread: SaveAs() turn file to read only

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Unhappy SaveAs() turn file to read only

    hello guys, i'm trying to make some changes on 2 files and then later save the changes but each time i debbug the program the files that i used turn into read only can you please tell me what im doing wrong in the save part


    Code:
      ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
      ws2 = xlApp.Workbooks.Open(TextBox2.Text).Worksheets(ComboBox2.SelectedItem)
      work1 = xlApp.Workbooks.Open(TextBox1.Text)
      work2 = xlApp.Workbooks.Open(TextBox2.Text)
    
    " i make some changes to the files in textbox1 and textbox2 here the code is so big "
    
     ws2.SaveAs(TextBox2.Text)
     work2.Close()
     ws1.SaveAs(TextBox1.Text)
     work1.Close()
     xlApp.Quit()
     releaseObject(xlApp)
     releaseObject(work1)
     releaseObject(work2)
    
    Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    i guess that i'm doing something wrong in the saveas() function . that you so much guys on correcting me if i wrong

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,597

    Re: SaveAs() turn file to read only

    My guess is if you look at Task Manager you will see instances of Excel because it isn't closed properly. I don't see exactly why in your code but here is an example of proper closing I got from here:

    Code:
    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'http://www.vbforums.com/showthread.php?728549-MS-Excel-2010-with-VB-net-close-opened-excel-file
            Dim Proceed As Boolean = False
    
            Dim xl_In As Excel.Application = Nothing
            Dim xlWorkBooks_In As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
    
            xl_In = New Excel.Application
            xl_In.DisplayAlerts = False
            xlWorkBooks_In = xl_In.Workbooks
            xlWorkBook = xlWorkBooks_In.Open("C:\WA Claim ten rows.xlsx")
    
            xl_In.Visible = False
    
            xlWorkBook.Close()
            xl_In.Quit()
    
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks_In)
            ReleaseComObject(xl_In)
    
        End Sub
    
        '~~> Release the objects
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: SaveAs() turn file to read only

    Thank you so much for answering , i guess that we are doing the same thing in the code but it still do not work each time i work with an excel it turns it to read only. btw where do i find the task manager please ?

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,603

    Re: SaveAs() turn file to read only

    Ctl+Alt+Del will take you to a screen which has the Task Manager as an option. There are likely other ways to get to it, but that's the one I always remember.

    It's a very good thing to look at. There is a real tendency with code to leave versions of Excel running. All you have to do is not close something, and it will remain in the background. You'd see it in Task Manager. If you still have the file open in some hidden Excel version, then any attempt to open the same file again would result in it being opened Read-Only. The file wouldn't be Read-Only, exactly, it would just be locked for editing by the process that opened it first.
    My usual boring signature: Nothing

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,597

    Re: SaveAs() turn file to read only

    I didn't test my original post. This is tested and releases Excel. One difference is setting xl = Nothing

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            Dim xl As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
    
            xl = New Excel.Application
            xl.DisplayAlerts = False
            xlWorkBooks = xl.Workbooks
            xlWorkBook = xlWorkBooks.Open("C:\TestIt.xlsx")
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()
    
            xlWorksheet.Cells(1, 2).Value = "XX"
    
            xl.Application.ActiveWorkbook.SaveAs("C:\TestItOut.xlsx")
    
    
            xlWorkBook.Close()
            xlWorksheet = Nothing
    
            xl.Quit()
            xl = Nothing
    
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xl)
    
            GC.Collect()
    
    
    
        End Sub
    
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
                Exit Sub
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    
    End Class
    Please remember next time...elections matter!

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: SaveAs() turn file to read only

    Thank you so much for the respond i tried your solution bur now i have a problem that didn't use to happen . in the releaseComObject function i get the error "object reference not set to an instance of an object" do you know where is this problem coming from ? this are the definitions that i have
    Code:
        Dim workbook As Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlApp As New Microsoft.Office.Interop.Excel.Application

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,597

    Re: SaveAs() turn file to read only

    My post was missing this at the top:

    Imports Excel = Microsoft.Office.Interop.Excel
    Please remember next time...elections matter!

  8. #8
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    717

    Re: SaveAs() turn file to read only

    you need both :

    Code:
    Imports  Microsoft.Office.Interop.Excel
    Imports  Microsoft.Office.Interop
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: SaveAs() turn file to read only

    Thank you guys for answering but that's not the problem because in my original code i have all what you mentionned but still get the same problems the files turn int read only when i save them

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,597

    Re: SaveAs() turn file to read only

    Quote Originally Posted by highfly884 View Post
    Thank you guys for answering but that's not the problem because in my original code i have all what you mentionned but still get the same problems the files turn int read only when i save them
    I have to disagree you have it all because the code I posted works but I don't know what you are doing in between. I've struggled with this issue off and on for years. I have a post here somewhere on this site where I proved the try/catch was doing it. If you have one take it out for grins. Also I have seen issues about using double .. (periods/dots). There is a poster here, I think it is KarenInstructer, that has posted many times on this issue. Use the search feature in the upper right and search on "close excel" and "release excel". You will get a lot of hits. Also look at this:

    https://www.vbforums.com/showthread....6-(or-VB5-VBA)
    Please remember next time...elections matter!

  11. #11

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: SaveAs() turn file to read only

    Thank you so much guys i will try to find a solution because it's seriously not working hhh thank you so much again

  12. #12
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    717

    Re: SaveAs() turn file to read only

    Stupid question : did you check that you have the access to the folder? did you try to change the location ?
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width