Results 1 to 9 of 9

Thread: vb.net changes excel file into read only

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    vb.net changes excel file into read only

    Hi all,
    I use an excel sheet to feed into the listbox on my vb.net form.However when I go to open the excel file after working with vb.net the file comes up read only and wont allow editing unless I save the workbook under another name.
    And when I do try and close the excel workbook it takes about thirty goes at closing.
    Anybody know anything about this or have any suggestions.Ive tried going to file>properties and uncheck the read only box,but the box was already unchecked .
    Ragards
    LL
    Last night I dreamt I went to Manderley again

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,486

    Re: vb.net changes excel file into read only

    Without knowing anything more about your code, it sounds like the problem is that you're not properly closing/disposing of your Excel objects in your project when you're done with the file, and so it's leaving the workbook in a locked state.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: vb.net changes excel file into read only

    Hi Techgnome,

    Many thanks for the reply.I have placed the code I used below,it has a piece of close code at the end...Excel.Quit()...but you could be right and this might not be doing its job.


    Code:
      Dim oExcel As Object = CreateObject("Excel.Application")
            Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\berty\Desktop\bertielist.xlsm")
            Dim oSheet As Object = oBook.Worksheets(1)
            Dim i As Integer
            Dim cell As String
            For i = 1 To AscW(bertieList.Items.Count.ToString()(i = i + 1)) - 1
                'set cell name, e.g. A1, A2, etc
                cell = "I" & Convert.ToString(i + 1)
                ' get cell data from Excel
                cell = oSheet.Range(cell).Value
                If cell = "" Then
                    Exit For
                Else
                    bertieList.Items.Add(cell)
    
                End If
            Next
            oExcel.Quit()
        End Sub
    Last night I dreamt I went to Manderley again

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: vb.net changes excel file into read only

    Hi Techgnome,

    Many thanks for the reply.I have placed the code I used below,it has a piece of close code at the end...Excel.Quit()...but you could be right and this might not be doing its job.


    Code:
      Dim oExcel As Object = CreateObject("Excel.Application")
            Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\berty\Desktop\bertielist.xlsm")
            Dim oSheet As Object = oBook.Worksheets(1)
            Dim i As Integer
            Dim cell As String
            For i = 1 To AscW(bertieList.Items.Count.ToString()(i = i + 1)) - 1
                'set cell name, e.g. A1, A2, etc
                cell = "I" & Convert.ToString(i + 1)
                ' get cell data from Excel
                cell = oSheet.Range(cell).Value
                If cell = "" Then
                    Exit For
                Else
                    bertieList.Items.Add(cell)
    
                End If
            Next
            oExcel.Quit()
        End Sub
    Last night I dreamt I went to Manderley again

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,486

    Re: vb.net changes excel file into read only

    You need to 1) set the worksheet to nothing, 2) close the workbook, and 3) set it to nothing then, 3) exit excel...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,823

    Re: vb.net changes excel file into read only

    Quote Originally Posted by limelight View Post
    Hi Techgnome,

    Many thanks for the reply.I have placed the code I used below,it has a piece of close code at the end...Excel.Quit()...but you could be right and this might not be doing its job.


    Code:
      Dim oExcel As Object = CreateObject("Excel.Application")
            Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\berty\Desktop\bertielist.xlsm")
            Dim oSheet As Object = oBook.Worksheets(1)
            Dim i As Integer
            Dim cell As String
            For i = 1 To AscW(bertieList.Items.Count.ToString()(i = i + 1)) - 1
                'set cell name, e.g. A1, A2, etc
                cell = "I" & Convert.ToString(i + 1)
                ' get cell data from Excel
                cell = oSheet.Range(cell).Value
                If cell = "" Then
                    Exit For
                Else
                    bertieList.Items.Add(cell)
    
                End If
            Next
            oExcel.Quit()
        End Sub

    well I take no chances at closeing Excel, here a sample
    I highlighted the part(s) for closeing.


    Code:
    Option Strict On
    
    Imports Excel
    
    
    Public Class Form2
    
       
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim a As Integer
            Dim Exceldaten(900, 2) As Double
    
            Dim Rdm As New Random
            For i = 0 To 900
                Dim Val As Double = Rdm.Next(1, 100) / 100
                For a = 0 To 900
                    Exceldaten(a, 0) = Rdm.Next(1, 1000) / 100
                    Exceldaten(a, 1) = Rdm.Next(1, 1000) / 100
                    Exceldaten(a, 2) = a + 1
                Next
            Next
    
    
    
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
            oXL.Visible = True
    
            'Data to Excel 
            oSheet.Range("A1").Value = "Header 1"
            oSheet.Range("B1").Value = "Header 2"
            oSheet.Range("c1").Value = "Nr."
    
            'select the Range where to put the Data:
            oSheet.Range("A2", "C900").Value = Exceldaten
            'or set Range ´D2 F900
            'oSheet.Range("D2", "F900").Value = Exceldaten
    
            'Save Excel
            oSheet.SaveAs("C:\E99.xls")
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
    
        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
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: vb.net changes excel file into read only

    hello limelight did you find a solution for your problème please ? cause i have the same one

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

    Re: vb.net changes excel file into read only

    Run a few tests and check the task manager. If you have a bunch of Excel instances running you are not closing properly.
    Please remember next time...elections matter!

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

    Re: vb.net changes excel file into read only

    Here is a sample I got from here. The link is at the top:
    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!

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