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?
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?