Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unable to open another excel file (when one Excel is opened by .net)

I have designed a .net application which will open an Excel file at the time of login and use it to print a report. It will be closed while logging out the user. I set visible to false for Excel file, so that user doesn't know about the background process.

But if anybody opens any other Excel file during this time, my report Excel file becomes visible and the Excel object is collapsed. I have to go to task manager and kill the all open Excel instances to fix this.

Code:

 Private Sub OK_Click(sender As Object, e As EventArgs) Handles OK.Click
        Try
            Dim dt As New DataTable()
            Dim Adapter As New SqlDataAdapter()
            ConnectMe()
            Dim SQLCmd As New SqlCommand("uspLogin", Con)
            SQLCmd.CommandType = CommandType.StoredProcedure
            SQLCmd.Parameters.AddWithValue("@pLoginName", UsernameTextBox.Text.Trim())
            SQLCmd.Parameters.AddWithValue("@pPassword", PasswordTextBox.Text.Trim())
            Adapter.SelectCommand = SQLCmd
            Adapter.Fill(dt)
            SQLCmd.Dispose()
            If dt.Rows.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor
                Loading.Show()
                OpenAllTempaltes()            
                Me.Hide()                
                Con.Close()
                Me.Cursor = Cursors.Arrow
            Else
                MsgBox("Your Credential is Wrong !!!", MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Login")
                UsernameTextBox.Text = ""
                PasswordTextBox.Text = ""              
                UsernameTextBox.Focus()
            End If
        Catch ex As Exception         
            Application.Exit()
        End Try
    End Sub

 Public Sub OpenAllTempaltes()
        Try                                   
            xlWorkBook = xlApp.Workbooks.Open(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Templates", "Excel_Templates_GST.xlsm"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)                
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub CloseAllTempaltes()
        Try
            CleanUp(xlApp, xlWorkBook, xlWorkSheet)
        Catch ex As Exception
            ExceptionLog("PrintPage", "CloseAllTempaltes", ex.ToString(), DateTime.Now.ToString("dd-MMM-yyyy"))
        Finally
            GC.Collect()
        End Try
    End Sub

How can I prevent this?

like image 993
Vignesh Kumar A Avatar asked Jul 10 '17 10:07

Vignesh Kumar A


People also ask

Why can't I open Excel on Windows 10?

One of the causes of Excel won’t open Windows 10 is that your Excel is set to ignore other applications that use DDE. The function of the DDE is to send a message to Excel once you double-click a file. Thus, Excel instructs to open the file.

How do I open multiple Excel files at once?

In File 1 shrink the window so you can see both the excel file and your list of other excel files. In file 1 double click any cell so that you see the cursor blinking in the cell waiting for input. Now double click on another excel file to try and open it.

Why does the open() call throw an error when running Microsoft Excel?

But when I run it with "C:\" and "scratch.xlsx", the Open () call throws the following error: Microsoft Excel cannot access the file 'C:\scratch.xlsx'. There are several possible reasons: • The file name or path does not exist.

Why does excel say XLS could not be found?

When you double-click a Microsoft Excel file type (.xls), Excel starts as expected, but then you may receive an error message that is similar to the following: C:\ filename .xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.


2 Answers

Use the IgnoreRemoteRequests property of the Excel application object:

xlApp.IgnoreRemoteRequests = True

This is the equivalent of checking the Excel UI option at
File | Options | Advanced | General | Ignore other applications that use Dynamic Data Exchange (DDE).
(See this related answer on SuperUser.)

I couldn't conveniently reproduce your scenario with a .NET application, but ran some tests by late binding an Excel.Application object from Word VBA and it worked as intended. I created a hidden Excel application, and was able to perform actions on it before and after opening files by double-clicking in File Explorer.

In my tests, the setting was not still toggled on the next time I opened Excel normally, but you might want to capture its value and restore it before quitting your application object, in case that behavior isn't universal.

Edit: This behavior has been around since at least Excel 2003, and I verified using Excel 2016 (32-bit).

In Excel 2013 or later, Excel switched to a single document interface: each workbook opens in its own window.

At least through 2016, the Visual Basic Editor has remained a multiple document interface, and you can easily see which files are open in an application session by looking at the Project Explorer pane in the VBE.

like image 157
AjimOthy Avatar answered Oct 16 '22 14:10

AjimOthy


Looks like your problem is caused by the same instance of xlApp. What I would do is simple: I would initialize new instance of xlApp and then quite that instance of the application. This way it won't interfere with any other open instance of Excel. Here is how I use it in C#:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlApp = new Excel.Application();  // by default this is invisible

do whatever you have to do with the new instance of xlApp and then quite the application. Once you quite the application it won't be in your task manager.

xlApp.Application.Quit();

I have designed several applications where I had to process the same Excel files that most likely would've been open by those same users. This approach (opening new instance of excel in invisible mode and then quitting the application) never caused any problems for me.

like image 36
Richard Mneyan Avatar answered Oct 16 '22 15:10

Richard Mneyan