Excel Application.Visible = true does not work .. SOMETIMES.

[dylanmendes]

Hello all,

 

My ASP.NET application attempts to export data into an Excel workbook using the following code:

 

Dim excelFileRef As Excel.Application

excelFileRef = New Excel.Application

 

Dim wbookNew As Excel.Workbook

wbookNew = excelFileRef.Workbooks.Add

 

'Save Instructions to Excel Workbook

InsertStringInExcel(wbookNew, strInstructions)

 

'Write data to Excel Workbook

InsertDataInExcel(wbookNew, dtableAnalysis)

 

excelFileRef.Visible = True

 

This code works fine on about 2/3 machines. However, when run on one of my client's machine, the Excel sheet does not open up. When I stepped through code here, it worked OK with no errors, except that the Excel application did not pop up when excelFileRef.Visible = True executed! Further, when I checked the processes in Tast manager, there was a new EXCEL.EXE process.

 

What do I need to do to allow my client to see the Excel application?

[techmanbd]

Your client may have a different version of excel. If that is true then you may need to use what they call automation. I had a similar problem. I was using 10.0 on my computer that I coded with, and when I installed it to the workstations, they had 9.0 and it wouldn't work.

 

goto this link and it explains automation and has an example. The person asks the question, then be sure to scroll down furthur because there is an AD between the question and the answer.

 

http://beta.experts-exchange.com/Pr...Q_20697219.html

[mark007]

Without seeing your full code it's hard to say but I would suggest it's probably one of:

 

1. Hidden references

 

Make sure that your code does not make any references to intrinsic VBA objects such as Activesheet etc. but instead references them via the application object e.g. xlApp.Activesheet

 

2. Not disposing of the COM components satisfactorily

 

Make sure you dispose of any global COM references properly using a function such as:

 

Private Sub DisposeObject(ByVal obj As Object)
       Dim count As Integer
       Try
           If obj Is Nothing Then Exit Try
           count = Marshal.ReleaseComObject(obj)
           While count > 0
               count = Marshal.ReleaseComObject(obj)
           End While
       Catch ex As Exception
       Finally
           obj = Nothing
       End Try
   End Sub

 

:)

[dylanmendes]

Thanks to both of you for replying.

I did try reinstalling Excel on his laptop but same error. I've now confirmed that both of us have the same Excel version.

 

Regarding the disposal of COM components - is that an issue since I haven't gotten to the point of disposal yet? I don't understand why, without throwing an error, the application does not show up on just this one guy's computer! With everything seeming identical I might add.

 

I'm not sure I understand your comment on Intrinsic references, Mark. I have pasted another method from my code. This combined with the earlier method really covers everything of interest on the Excel front. Do you think you could point out with an example what type of referencing I SHOULD be using.

 

Appreciate your help!

 

Private Sub InsertDataInExcel(ByRef wbookData As Excel.Workbook, ByVal dtableAnalysisData As DataTable)

Try

Dim row As Integer

Dim col As Integer

Dim rowCount As Integer

Dim colCount As Integer

Dim cell As String

Dim rowcell As Integer

 

Dim excelWorksheet As Excel.Worksheet = wbookData.Worksheets(1)

excelWorksheet.Name = "Analysis Data"

' get count of rows and count of columns

rowCount = dtableAnalysisData.Rows.Count()

colCount = dtableAnalysisData.Columns.Count()

 

' add the column headings

For col = 0 To colCount - 1

row = 1

cell = GetExcelColumn(col) & row.ToString

excelWorksheet.Range(cell).Value = dtableAnalysisData.Columns(col).ColumnName

'excelWorksheet.Range(cell).ColumnWidth = grdFieldnetData.TableStyles(0).GridColumnStyles(col).Width / 4

Next

 

' now add the data elements

For row = 0 To rowCount - 1

rowcell = row + 2

For col = 0 To colCount - 1

cell = GetExcelColumn(col) & rowcell.ToString

excelWorksheet.Range(cell).Value = dtableAnalysisData.Rows(row)(col).ToString()

Next

Next

 

'Finally format the header row

Dim rngHeader As Excel.Range

rngHeader = excelWorksheet.Range("A1").EntireRow

rngHeader.AutoFit()

rngHeader.Columns.ColumnWidth = 25

rngHeader.Font.Bold = True

'rngHeader.Font.Color = grey

 

Catch ex As RethrownException

Throw ex

End Try

End Sub

 

Without seeing your full code it's hard to say but I would suggest it's probably one of:

 

1. Hidden references

 

Make sure that your code does not make any references to intrinsic VBA objects such as Activesheet etc. but instead references them via the application object e.g. xlApp.Activesheet

 

2. Not disposing of the COM components satisfactorily

 

Make sure you dispose of any global COM references properly using a function such as:

 

Private Sub DisposeObject(ByVal obj As Object)
       Dim count As Integer
       Try
           If obj Is Nothing Then Exit Try
           count = Marshal.ReleaseComObject(obj)
           While count > 0
               count = Marshal.ReleaseComObject(obj)
           End While
       Catch ex As Exception
       Finally
           obj = Nothing
       End Try
   End Sub

 

:)

Edited August 31, 2005 by dylanmendes

[mark007]

Ok, I have a couple of queries:

 

1. Is this really an asp.net application? In that case the code is running on a webs server and will open Excel on the webserver rather than client machine..

 

2. When you first run it how many excel instances are in the task manager? When you run-it again how many are there?

 

3. Does it ever work in this problem PC e.g. on the first occasion?

 

A couple of other points. When automating Excel (in fact really it's always good practice) it's best to ensure Option Strict is set. To will make sure all your object assignments are valid and give the asddeed advantage of axtra intellisense.

 

Also when you post your code enclose it in [vb ][/ vb] tags (less the spaces) to format your code as in my post above.

 

:)

[dylanmendes]

Hiya- thanks for the prompt response and apologies for the poorly formatted code!

 

I actually signed on to share with you an idea I had and Lo! i see your reply :)

First let me answer your questions-

1) This is an ASP.NET application. But the webserver and the client are the same.

 

2) I'm not sure about this cause the client is in a different geography, but I will confirm this tomorrow. However, going by the way the app behaves on my computer - initially there are no instances; after the first run, the Excel.Application object is reused so there will be just one.

 

3) This has never worked on the PC in question.

 

Now for the interesting part:

I created two test projects that ONLY performed the simple task of opening an Excel application and making it visible on the click of a button. One of these applications was a Web app with ASP.NET and the other was a Windows Forms app in VB.NET. I found that the VB.NET app opens the Excel and makes it visible but the ASP.NET does not.

 

I am led to believe that this has something to do with the ASP.NET user account! My suspicions are strengthened by the fact that this particular user has had issues with rights to folders etc. while the others have not. He is not an admin on his PC. What's the missing piece to have him see the application?

 

Cheerio and thanks,

Dylan

 

 

Ok, I have a couple of queries:

 

1. Is this really an asp.net application? In that case the code is running on a webs server and will open Excel on the webserver rather than client machine..

 

2. When you first run it how many excel instances are in the task manager? When you run-it again how many are there?

 

3. Does it ever work in this problem PC e.g. on the first occasion?

 

A couple of other points. When automating Excel (in fact really it's always good practice) it's best to ensure Option Strict is set. To will make sure all your object assignments are valid and give the asddeed advantage of axtra intellisense.

 

Also when you post your code enclose it in [vb ][/ vb] tags (less the spaces) to format your code as in my post above.

 

:)

[PlausiblyDamp]

What operating systems are each of the PCs running? Do they differ between the working ones and the non-working ones?

[dylanmendes]

Hi PD,

 

The working set includes both Windows 2000 as well as XP.

The PC with the problem is Windows XP. One difference between the working and the non-working ones is that the non-working PC does not have Admin rights.

 

thanks for checking in..

Dylan

 

What operating systems are each of the PCs running? Do they differ between the working ones and the non-working ones?

[PlausiblyDamp]

Although this could be related to a permissions issue this behaviour could also differ between different OSs as Excel will (should?) run under the ASPNET account, this will often result in it running on it's own desktop rather than the user desktop.

Personally I would look at the way this is being expected to work - web applications are just not designed to do what you are attempting to do, if the software is going to be installed locally why not just create a windows application? If you really need this to be a web application then it should be expected to have the application on a remote server.

[dylanmendes]

Unfortunately, we're too far down the pike to change the setup right now. I will be trying stuff out with the client within a few hours. Will keep the thread posted.

 

Cheers,

Dylan

 

Although this could be related to a permissions issue this behaviour could also differ between different OSs as Excel will (should?) run under the ASPNET account, this will often result in it running on it's own desktop rather than the user desktop.

Personally I would look at the way this is being expected to work - web applications are just not designed to do what you are attempting to do, if the software is going to be installed locally why not just create a windows application? If you really need this to be a web application then it should be expected to have the application on a remote server.

[mark007]

You could try changing the username under process model of the machine.config file to system. This will get the app to run under the local system (high privilege) account. This might flush out if it's a security issue.

 

Discussing this with a couple of others:

 

Mike_R says:

 

I have seen this sort of sillyness using straight VB.Net or C#. In particular, my VB6 COM Excel DLL's seem to prevent Excel from being automated via .Net. It's totally amazing, but Excel will not load, or will load but can't be made visible, etc. Behaviour pretty similar to what this guy is showing.

 

And Insomniac says:

 

OK, it may be as simple as Ignore other applications being checked in Tools>>Options>>General

 

:)

[dylanmendes]

Mike and everyone,

 

I tried running it with username="SYSTEM" under process model and it doesn't work - it's still invisible.

 

I've also tried the Tools-->General--->Ignore other applications setting and it is unchecked, so that's fine too.

 

Any more ideas.. anything??? - I'm in HUGE trouble :(

 

 

You could try changing the username under process model of the machine.config file to system. This will get the app to run under the local system (high privilege) account. This might flush out if it's a security issue.

 

Discussing this with a couple of others:

 

Mike_R says:

 

 

 

And Insomniac says:

 

 

 

:)

[mark007]

Do they have any non MS Excel addins installed? If so try uninstalling them..

 

Another thought perhaps is to save the workbook you create then close excel in your app. Finally shellexecute excel to start opening the workbook.

[PlausiblyDamp]

It would still be launched under the desktop of the executing process (the web server) not the user's desktop. The problem is that this is not how web applications are designed to work, and as such are not working as desired.

If this is ever expected to run as a client server app then Excel will be launched on the server not the client, depending on IIS / ASPNET configuration and permissions it may still not be visible on the client even with the web server running locally.

Elevated permissions (i.e. System) could be a major problem if a macro virus got onto the PC, could potentially cause further exploits (invalid input into the webform etc).

Edited September 1, 2005 by PlausiblyDamp

[dylanmendes]

Mark, that's awesome! I tried the workaround and it works. I mean, I don't think popping up the Excel sheet is a big deal with the client and I'd like to stay away from the shell command. But I'd forgotten that I could just try and save it!

 

Thank you so much for all your help!

 

Thanks everyon!!!

 

Do they have any non MS Excel addins installed? If so try uninstalling them..

 

Another thought perhaps is to save the workbook you create then close excel in your app. Finally shellexecute excel to start opening the workbook.

[mark007]

It would still be launched under the desktop of the executing process (the web server) not the user's desktop. The problem is that this is not how web applications are designed to work, and as such are not working as desired.

 

Agreed.

 

Elevated permissions (i.e. System) could be a major problem if a macro virus got onto the PC, could potentially cause further exploits (invalid input into the webform etc).

 

Sure - I only meant it as a test!

 

Mark, that's awesome!

 

Glad you got it sorted!

 

:D