2

Background: I am using the Microsoft.Office.Interop.Excel libraries to open, manipulate, and save excel files in my Windows Form program. For reasons I won't fully go into, I need the information presented on the screen at any one time to be minimal, so I am using DataTables and DataGridViews to manipulate the information stored in the excel file. I can pull in and edit the information just fine, but my issue is with saving.

My Issue:

When attempting to save back out to the excel file using the WorkBook.Save() function, the changes are not reflected when I open the file in Excel. As a test, I tried the .SaveAs() function to see what was up and got the error that the original file was opened in read-only mode. I used the .SaveCopyAs() function to ensure my information was being outputted correctly, and the saved copy contained the changes I made.

I have attempted every possible combination of any and all read-only and save properties I could find, and the following two properties seem to solve the issue for most other people but will not work for me.

xlWorkBook = xlApp.Workbooks.Open(currFile, IgnoreReadOnlyRecommended:=True, ReadOnly:=False)

My code is spread out across several functions as I am using buttons and menu items to operate the program, but it follows the same workflow as the Save example from this website, except I set xlApp.Visible = False: http://www.siddharthrout.com/2012/09/12/saving-and-closing-the-excel-file-savesave-as-method/.

I'd appreciate any help.

Edit: Forgot to mention, I am using Excel 2010, and Visual Studio 2015 with the VB Windows Form Application template.

pmackni
  • 307
  • 3
  • 12
  • That line of code tries to assign the [default value](https://stackoverflow.com/a/19200523/11683) of a Workbook (which it does not have) to a predefined Excel enum value `XlWindowType.xlWorkbook`. Rename your variable and [use `Set`](https://stackoverflow.com/a/17877644/11683). – GSerg Jul 31 '17 at 16:50
  • Maybe - but it sounds as though pmackni is importing the data into a datatable that he turns into a datagridview which is then editted. Now he wants to take the datatable and put back into the excel file so this Saveas will not work – codeMonger123 Jul 31 '17 at 17:16
  • codeMonger123 is right, that's the approach I am going with. And as for the Set route, the version of VB I am using no longer supports Sets and Lets, so that solution will not work for me. I am declaring the xlWorkBook variable with this line of code: `Private xlWorkBook As Excel.Workbook`. – pmackni Jul 31 '17 at 17:35
  • Would it be applicable/kosher to open the file, read the contents to my DataTables, then close the file, only reopening it when I Save/Export the info back to the file, or maybe just Save As over the exiting file? I'd prefer not doing that, but if I can't get Workbook.Save() to work, that's the only route I can think of to take. – pmackni Jul 31 '17 at 17:47
  • Put a check for `Workbook.ReadOnly` in your code after you open it. The `ReadOnly=False` argument in the `Open` statement will not prevent the `Workbook` from opening in ReadOnly mode. Some other process or an improperly shutdown instance) may have a lock on the file. Improperly shutdown instances are easy to generate when debugging interop code. They will show up in Task Manager as Excel, but are located in the "Background" processes list. – TnTinMn Jul 31 '17 at 17:54
  • `Workbook.ReadOnly` returned true after the `Open` line. Due to other errors arising, I've been leaving Task Manager open and monitoring when EXCEL.EXE *32 appears and ending it if nothing is using it, so background processes should not be causing the issue. It may be that another process entirely that I am unaware of is preventing the changes. – pmackni Jul 31 '17 at 18:11
  • Have you checked the file's `ReadOnly` property in Explorer? Or could it be a permission issue in the target directory? – TnTinMn Jul 31 '17 at 18:24
  • Both the `Read` and `Write` properties for the file are allowed in explorer, and I plan for this to be a client side application, so my current target directory (at least for testing) is the `Documents Folder` under `C:\Users\`, which has not given me issues in any prior coding applications. Could it be the way I'm accessing that folder? This is how I'm setting it: `openFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)` – pmackni Jul 31 '17 at 18:31
  • If the path was an issue, your would not be able to open the file. I'm not convinced that you do not have a hidden Excel instance. Try this code in your app: `Dim wb As Excel.Workbook = DirectCast(System.Runtime.InteropServices.Marshal.BindToMoniker(pathToFile), Excel.Workbook) : wb.Application.Visible = True : wb.Application.UserControl = True : wb.Windows(1).Visible = True`. This will either attach to an existing Excel instance of the file or create a new instance. The close the Excel UI down. Note if it indicates the file is readonly. – TnTinMn Jul 31 '17 at 19:03
  • That indicated the File was [Read Only] in the file name at the top of the UI, so there must be an unexpected instance running somewhere. Now I have to ask just to make sure, but while I have the file open with the `Open` method and before I close it, I am supposed to see one instance of EXCEL.EXE *32 while my app is running, correct? That seemed to be the indication from what I could find, but now I want to make sure that's expected. – pmackni Jul 31 '17 at 19:13
  • An interop opened instance will show in Task Manager. if its Visible property is False, it will show as background process. Try running the code I provided above a few times. If all you get is ReadOnly instances, a reboot may be the quickest solution. Hopefully the issue goes away at that point. – TnTinMn Jul 31 '17 at 19:26
  • Okay, that's what I thought. I ran the code several times as you suggested, then restarted my computer and ran it a few more times with just Visual Studio and the app open, and it still says [Read Only] next to the file name every time. – pmackni Jul 31 '17 at 20:19
  • I created a brand new vb file containing nothing other than the line of code you provided, and it opened without the [Read Only] tag next to the file name, meaning the issue is likely how I open or store the file from within my Open sub. Could OpenFileDialog be creating some interference? – pmackni Jul 31 '17 at 21:03
  • Just a thought, where did the excel file originate? Office 2010+ has a tendency of not trusting downloaded excels etc, so if you just fire up your excel and open that file, will excel open it as readonly? Sometimes you need to set options in your Excel's trust center, sometimes a mere re-saving of the Excel file with your Excel does the trick. – Amao Mao Aug 01 '17 at 00:57
  • I looked into that, but that didn't seem to release the file to my program in any way. The issue is my program is reading it as Read Only, while Excel can open, edit, and save freely. – pmackni Aug 01 '17 at 12:50

1 Answers1

0

I found the answer to my issue. I was able to reliable open the file in read/write or read-only modes by moving the line of code TnTinMn provided me around my open method. It was not a ghost instance of excel running, at least not really: You can't open a file in read/write mode when it's still open with another method in the same program. The method I found for opening a file looked more or less like this (this is a very boiled down version from what I had, so it will be a much more noticeable issue now than when I was dealing with it):

If openFileDialog1Result = System.Windows.Forms.DialogResult.OK Then
    Try
        myStream = openFileDialog1.OpenFile()

        If myStream IsNot Nothing Then
            wb = DirectCast(Marshal.BindToMoniker(currFile), Excel.Workbook) : wb.Application.Visible = True : wb.Application.UserControl = True : wb.Windows(1).Visible = True
            'Do work
        End If
    Catch Ex As Exception
        MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
    Finally
        ' Check this again, since we need to make sure we didn't throw an exception on open.
        If myStream IsNot Nothing Then
            myStream.Close()
        End If
    End Try
End If

Now that I know what the issue is, I feel as though it should have been glaringly obvious that if I am trying to store the file I'm opening through Excel.Workbook.Open() or any other method while I still have it open with openFileDialog1.OpenFile(), of course it's only going to be able to open in read only. I unfortunately did not catch this sooner because I have a lot more going on than the example snippet before. I found that I do not even need to use stream for my purposes, and my code now follows this structure:

If openFileDialog1Result = System.Windows.Forms.DialogResult.OK Then
    Try
        currFile = openFileDialog1.FileName

        wb = DirectCast(Marshal.BindToMoniker(currFile), Excel.Workbook) : wb.Application.Visible = True : wb.Application.UserControl = True : wb.Windows(1).Visible = True
    Catch Ex As Exception
        MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
    End Try
End If

I appreciate all the help that I received, and I apologize for taking you all on a wild goose chase.

pmackni
  • 307
  • 3
  • 12