Vignesh Kumar Vignesh Kumar - 7 days ago 4
Vb.net Question

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


Please help me out on how to prevent this.

Answer

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.