Excel COM application hangs on Workbooks.Open

O

owdiths

Hi there. This is my first time posting on this forun, but I have read
through much of the content, and I feel good about the participants. Here is
my problem...

I have written Excel Automation code to create import templates for
transfering data from Excel to VB Objects. This code works nicely for some
spreadsheets, but hangs up for others. I have run the code in the .NET
debugger and I found that it hangs up on the open statement. When I had this
problem before, it had to do with the macro security popup. However, this
does not seem to be the case here, as I have set macro security to low, and
when I open the spreadsheet manually, I do not get any popups. The pertinant
code is as follows:

'Excel object variables
Dim xlApp As Application
Dim xlBooks As Workbooks
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim xlRange As Range
Dim xlCell As Range
Dim defaultColumns As Integer =
ConfigurationSettings.AppSettings("DefaultColumnCount")
Dim defaultRows As Integer =
ConfigurationSettings.AppSettings("DefaultRowCount")

'Make sure we have a file
If mFileInfo.Exists Then

'Put the Excel functionality inside a Try...Catch so cleanup is still
performed even if there is an error
Try
Try
'See if Excel is running and assign it to the app variable if it is
xlApp = GetObject(Nothing, "Excel.Application")
Catch ex As Exception

End Try

'If not already running, create new instance
If xlApp Is Nothing Then
xlApp = New Application
'xlApp = CType(CreateObject("Excel.Application"), Application)
End If
'if No instance exists, error occured
If xlApp Is Nothing Then
'Response.Write("Could Not Start App")
Else
'This line forces the COM engine to disable macros without notification
'to keep the app from hanging
xlApp.AutomationSecurity =
Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForce Disable
'Turn off any other alerts. This forces Excel to use the default
'response to any user input dialogs
xlApp.DisplayAlerts = False

'Get the collection of workbooks in the Application
xlBooks = CType(xlApp.Workbooks, Workbooks)
'Open the specified file. Opened readonly to prevent save dialoge box
xlBook = CType(xlBooks.Open(mFileInfo.FullName,
XlUpdateLinks.xlUpdateLinksNever, True), Workbook)
'set sheet to first sheet in book
xlSheet = CType(xlBook.Worksheets(1), Worksheet)


'Code that loops through the cells goes here


'do Application cleanup
xlBook.Close(False)
xlApp.Quit()
While ReleaseComObject(xlApp) > 0
End While
xlApp = Nothing
'force a garbage collection
System.GC.Collect()

Catch ex As Exception
'If an error occurs, cleanup will happen anyway
HttpContext.Current.Response.Write(ex.Message & "<BR>" & ex.StackTrace &
"<BR>")
If Not ex.InnerException Is Nothing Then
HttpContext.Current.Response.Write(ex.InnerException.ToString & "<BR>")
End If
HttpContext.Current.Response.Write(mFileInfo.FullName)
'HttpContext.Current.Response.Write("<BR>" & xlCell.Value.GetType.ToString)
If Not xlApp Is Nothing Then
For j As Int16 = 1 To xlApp.Workbooks.Count
xlApp.Workbooks(j).Close(False)
Next
xlApp.Quit()
While ReleaseComObject(xlApp) > 0
End While
'ReleaseComObject(xlApp)
xlApp = Nothing
'force a garbage collection
System.GC.Collect()
End If
End Try

Else
Exit Function
End If


The line that the code hangs on is this one:

xlBook = CType(xlBooks.Open(mFileInfo.FullName,
xlUpdateLinks.xlUpdateLinksNever, True), Workbook)

As I noted above, this code works well for some spreadsheets, and not for
others. I can send you the two examples, if needed. I am at a loss, here...
Does anyone have any ideas?
 
G

gary.mcgill

If the workbook is password-protected, then that would cause Excel to
pop up an input box for the password. (Even though you have
DisplayAlerts set to False). There are probably other obscure reasons
why this could happen.

You can get around the password problem by supplying a (wrong) password
in the Open call - in which case an Exception will be raised if the
workbook is password protected, otherwise the password will be ignored.


Why not try making the Excel app visible, so that you can see exactly
what Excel is doing?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top