Workbook gets saved as readonly

I am trying to save a workbook but it saves as read only any suggestions?


 Public Function ReturnDevAreaPath() As String
        Dim strresponse As String
        Dim o As New SaveFileDialog
        ' Dim strwkname As String
        Dim ExcelBook, bookname As String
        o.Filter = "Excel File (*.xls)|*.xls"
        o.Title = "Save Excel Workbook As"
        o.Filter = "Excel Spreadsheets (*.xlsx)|*.xlsx"
        Dim str As String = "MedicalExcelReport"
        o.FileName = str

        strresponse = o.ShowDialog()
        Application.DoEvents()
        If strresponse = 1 Then
            Application.DoEvents()
            ExcelBook = o.FileName
            bookname = ExcelBook
            Return ExcelBook
        End If
    End Function

Open in new window

RIASAsked:
Who is Participating?
 
AndyAinscowFreelance programmer / ConsultantCommented:
Default open behaviour is read/write.

        Try
            Dim xlapp As Object
            xlapp = CreateObject("Excel.Application")
            ' Dim xlapp = New Excel.Application
            xlapp.Visible = True
            xlapp.Workbooks.Open(StrExcelLocation)
        Catch ex As Exception

After the Try block finishes the xlapp variable goes out of scope and is flagged for deletion by the GC.  I would suggest you have the Dim xlapp As Object at class (form) level.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
I don't actually see any 'save' being performed in that code.  Have you edited it?
0
 
RIASAuthor Commented:
The  strresponse = o.ShowDialog()  has save option in it
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
   xlApp.Workbooks(1).SaveAs(FilePath) this bit Andy errors :
cannot save as document was opened as readonly
0
 
Éric MoreauSenior .Net ConsultantCommented:
ShowDialog displays a dialog with some option to save your file but does not save the file. You need to save it! So there is something else elsewhere saving with the readonly attribute or your file is just not saved.
0
 
RIASAuthor Commented:
Function ExportToExcel(ByVal intCreateNew As Integer, ByVal dtGridData As DataTable, ByVal FilePath As String, ByVal StrSheetname As String, Optional ByVal xlApp As Excel.Application = Nothing) As String
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Application.DoEvents()
        Try
            FrmMain.Cursor = Cursors.WaitCursor

            xlApp.DisplayAlerts = False

            If intCreateNew = 0 Then
                Try
                    xlApp.Workbooks.Add()
                Catch ex As Exception
                    'Iff x1app is not found 
                    xlApp = New Excel.Application '*** Create excel app only once.
                End Try

            End If
            If IsNothing(FilePath) = True Then
                xlWorkSheet = Nothing
                xlWorkBook = Nothing
                xlApp = Nothing
                FrmMain.Cursor = Cursors.Default
                Return "Cancelled"
                Exit Function
            End If
            xlApp.Workbooks(1).SaveAs(FilePath)
            Application.DoEvents()

            xlWorkBook = xlApp.Workbooks.Open(FilePath,, False)
            Application.DoEvents()
            If intCreateNew = 0 Then
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            Else
                xlWorkBook.Worksheets.Add(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            End If
            xlWorkSheet.Name = StrSheetname
            With xlWorkSheet.PageSetup
                .PrintGridlines = True
                .CenterHeader = StrSheetname
                .Zoom = False
                .Orientation = Excel.XlPageOrientation.xlLandscape
            End With
            Dim dtRowCount As Integer = dtGridData.Rows.Count
            Dim dtColCount As Integer = dtGridData.Columns.Count

            Dim objXlColHeaderData(1, dtGridData.Columns.Count) As Object
            Application.DoEvents()
            For i As Integer = 0 To dtColCount - 1
                objXlColHeaderData(0, i) = dtGridData.Columns(i).ColumnName
            Next
            Dim objXlData(dtRowCount, dtColCount) As Object
            For iRow As Integer = 0 To dtRowCount - 1
                Application.DoEvents()
                For iCol As Integer = 0 To dtColCount - 1
                    Application.DoEvents()
                    If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then

                        Select Case StrSheetname.ToUpper
                            Case "DIRECTORY"


                                objXlData(iRow, iCol) = "'" & dtGridData.Rows(iRow).Item(iCol)



                            Case "INTERPRETER DIRECTORY"


                                objXlData(iRow, iCol) = "'" & dtGridData.Rows(iRow).Item(iCol)


                            Case "DIARY"


                                objXlData(iRow, iCol) = "'" & dtGridData.Rows(iRow).Item(iCol)



                            Case Else
                                objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
                                'objXlData(iRow, iCol) = "'" & dtGridData.Rows(iRow).Item(iCol)


                        End Select


                    Else
                        objXlData(iRow, iCol) = ""
                    End If
                Next
            Next
            Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)

            xlRange.Value2 = objXlColHeaderData
            xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, dtColCount)).Font.Bold = True
            Select Case StrSheetname.ToUpper
                'Formatting the dates on excel please check the column names  refer stored procedure for columns 
           

                Case "DIY"
                    xlRange = xlWorkSheet.Range("C1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("D1")
                    'xlRange.EntireColumn.NumberFormat = "hh:mm"
                    'xlRange = xlWorkSheet.Range("E1")
                    'xlRange.EntireColumn.NumberFormat = "hh:mm"
                    For Each strX As String In New String() {"D", "E"}
                        xlRange = xlWorkSheet.Range(strX + "1")
                        xlRange.EntireColumn.NumberFormat = "hh:mm"
                    Next
               
                        xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    Next

            End Select



            xlRange = xlWorkSheet.Range("A2")
            xlRange.EntireColumn.NumberFormat = "#,##0.00"
            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            ' xlRange = xlRange.Resize(dtRowCount + 1, dtColCount + 1)
            xlRange.Value2 = objXlData

            'With xlWorkSheet
            '    .Range(.Cells(1, 1), .Cells(1, 1)).Select()
            'End With
            With xlWorkSheet.Application.ActiveWindow
                .SplitColumn = 0
                .SplitRow = 1
            End With
            With xlWorkSheet
                .Cells.EntireColumn.AutoFit()
                .Cells.EntireRow.AutoFit()
                .Application.ActiveWindow.FreezePanes = True
            End With
            FrmMain.Cursor = Cursors.WaitCursor
            Application.DoEvents()
            CType(xlApp.ActiveWorkbook.Sheets(1), Excel.Worksheet).Select()
            xlWorkBook.SaveAs(,,,, False)

        Catch ex As Exception
            MessageBox.Show(ex.Message, "ErrorIn ExportToExcel", MessageBoxButtons.OK, MessageBoxIcon.Error)
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            FrmMain.Cursor = Cursors.Default
            Return "False"
        Finally
            FrmMain.Cursor = Cursors.Default
        End Try
        Return "True"
    End Function

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
You probably need to pass o.FileName to your SaveAs method
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>was opened as readonly

There is a big hint there why the save fails.  How did you open the excel doc ?
0
 
RIASAuthor Commented:
xlWorkBook = xlApp.Workbooks.Open(FilePath,, False)
0
 
D PatelD Patel, Software EngineerCommented:
You need to take ownership of the folder where the excel file is, just follow the steps below:

1. Locate the file or folder on which you want to take ownership in windows explorer
2. Right click on file or folder and select “Properties” from Context Menu
3. Click on Security tab
4. Click on “Advance”
5. Now click on Owner tab in Advance Security Settings for User windows
6. Click on Edit Button and select user from given Change Owner to list if user or group is not in given list then click on other users or groups. Enter name of user/group and click ok.
8. Now select User/group and click apply and ok. (Check “Replace owner on subcontainers and objects” if you have files and folder within selected folder)
9. Click ok when Windows Security Prompt is displayed
10. Now Owner name must have changed.
11. Now click Ok to exist from Properties windows

Once you have taken the ownership of file or folder next part comes is Granting Permissions to that file/folder or object.

How to Grant Permissions in Windows 7
1. Locate the file or folder on which you want to take ownership in windows explorer
2. Right click on file or folder and select “Properties” from Context Menu
3. Click on Edit button in Properties windows Click ok to confirm UAC elevation request.
4. Select user/group from permission windows or click add to add other user or group.
5. Now under Permission section check the rights which you want to grant i.e check “Full Control” under the “Allow” column to assign full access rights control permissions to Administrators group.
6. Click Ok for changes to take effect and click ok final ok to exit from Properties window.
0
 
D PatelD Patel, Software EngineerCommented:
Saving a file as read only can be a great way to share your files without other people being able to adjust the data. This tutorial will show you how to save excel as read only.

Step # 1 -- Choosing to Save As

Here we have just finished creating a spreadsheet that contains some cost information. Click the "File" button in the top right corner and then click on the "Save As" button in the new menu that has appeared.

Step # 2 -- Opening General Options

This will open the "Save As" window. At the bottom of the window there is a drop down list called "Tools", click on this, and in the menu that appears click the "General Options" button. This will open the "General Options" window.

Step # 3 -- Saving the File

At the bottom of this window is a check box that is called "Read-only recommended", select this option and click "OK". Now choose a destination for the file and name it. Finally click "Save" and that is how to save excel as read only.
0
 
RIASAuthor Commented:
Thanks Dhara, but I am looking for vb.net solution. Really appreciate your comment.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
The open command looks fine - should be read/write.
Does this happen with only workbooks you open, only workbooks you create or both ?
Are you making changes to the workbook, I assume you are.  If yes then it can mean this is one of those error messages which don't actually mean what they say.

Good point by Dhara, does this behaviour happen just in one particular folder or anywhere you attempt to save the workbook?
0
 
RIASAuthor Commented:
Andy ,the code posted is thats what I do.

Good point by Dhara, does this behaviour happen just in one particular folder or anywhere you attempt to save the workbook?

It happens irrespective of folder
0
 
RIASAuthor Commented:
Ahh Andy,

 Public Sub OpenExcel(ByVal StrExcelLocation As String)
        Try
            Dim xlapp As Object
            xlapp = CreateObject("Excel.Application")
            ' Dim xlapp = New Excel.Application
            xlapp.Visible = True
            xlapp.Workbooks.Open(StrExcelLocation)
        Catch ex As Exception
            FormatMessage(11, " Excel Update : ", StrExcelLocation, "Excel Update")
        End Try
    End Sub

Open in new window


here where it is opening ...any suggestion on passing read only :False
0
 
RIASAuthor Commented:
ok will try .
Andy,
Any suggestion on how to check if the excel file is already open and prompt the user to close it
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>Any suggestion on how to check if the excel file is already open and prompt the user to close it

Ask another question about that.
ps.  Search first, I'm pretty certain there are earlier questions here at EE about doing that.
1
 
RIASAuthor Commented:
Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.