Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel COM application hangs on Workbooks.Open
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("DefaultColumnCo unt") 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.msoAut omationSecurityForce 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.InnerExcepti on.ToString & "<BR") End If HttpContext.Current.Response.Write(mFileInfo.FullN ame) '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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel COM application hangs on Workbooks.Open
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Run for every open workbooks (one by one) | Excel Programming | |||
Excel hangs when trying to open a file but it's ok if i open exce. | Excel Discussion (Misc queries) | |||
Using workbooks.open after new instance of excel application | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Excel hangs on Workbooks.Open Line | Excel Programming |