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 clients 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 wouldnt 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 its hard to say but I would suggest its 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. Ive now confirmed that both of us have the same Excel version.

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

Im 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 its hard to say but I would suggest its 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

 

[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 its always good practice) its 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) Im 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. Whats 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 its always good practice) its 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 its 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, were 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 its 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 its 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 DLLs seem to prevent Excel from being automated via .Net. Its totally amazing, but Excel will not load, or will load but cant 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 doesnt work - its still invisible.

Ive also tried the Tools-->General--->Ignore other applications setting and it is unchecked, so thats fine too.

Any more ideas.. anything??? - Im 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 its 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 users 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).

 

[dylanmendes]

Mark, thats awesome! I tried the workaround and it works. I mean, I dont think popping up the Excel sheet is a big deal with the client and Id like to stay away from the shell command. But Id 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 users 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, thats awesome!

Glad you got it sorted!