Solved

Workbook gets saved as readonly

Posted on 2016-09-14
18
42 Views
Last Modified: 2016-09-20
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

0
Comment
Question by:RIAS
  • 9
  • 5
  • 2
  • +1
18 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41797689
I don't actually see any 'save' being performed in that code.  Have you edited it?
0
 

Author Comment

by:RIAS
ID: 41797692
The  strresponse = o.ShowDialog()  has save option in it
0
 

Author Comment

by:RIAS
ID: 41797701
   xlApp.Workbooks(1).SaveAs(FilePath) this bit Andy errors :
cannot save as document was opened as readonly
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41797703
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
 

Author Comment

by:RIAS
ID: 41797705
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41797709
You probably need to pass o.FileName to your SaveAs method
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41797711
>>was opened as readonly

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

Author Comment

by:RIAS
ID: 41797742
xlWorkBook = xlApp.Workbooks.Open(FilePath,, False)
0
 
LVL 5

Expert Comment

by:D Patel
ID: 41797745
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

Expert Comment

by:D Patel
ID: 41797748
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
 

Author Comment

by:RIAS
ID: 41798768
Thanks Dhara, but I am looking for vb.net solution. Really appreciate your comment.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41799200
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
 

Author Comment

by:RIAS
ID: 41799329
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
 

Author Comment

by:RIAS
ID: 41799332
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
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 41799370
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
 

Author Comment

by:RIAS
ID: 41799380
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41799422
>>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
 

Author Closing Comment

by:RIAS
ID: 41806706
Cheers!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now