Workbook gets saved as readonly

I don't actually see any 'save' being performed in that code.  Have you edited it?

The  strresponse = o.ShowDialog()  has save option in it

   xlApp.Workbooks(1).SaveAs(FilePath) this bit Andy errors :
cannot save as document was opened as readonly

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.

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

Select allOpen in new window

You probably need to pass o.FileName to your SaveAs method

>>was opened as readonly

There is a big hint there why the save fails.  How did you open the excel doc ?

xlWorkBook = xlApp.Workbooks.Open(FilePath,, False)

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.

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.

Thanks Dhara, but I am looking for vb.net solution. Really appreciate your comment.

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?

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

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

Select allOpen in new window

here where it is opening ...any suggestion on passing read only :False

ok will try .
Andy,
Any suggestion on how to check if the excel file is already open and prompt the user to close it

>>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.

Cheers!