Opening multiple Excel templates from ASP.Net

Hey all!

First off, I apologize if this is the incorrect place to put this question. I was unsure where to stick it and selected the clientside section because in all honesty, this is a problem that's located on the client's computer. :)

I have a simple ASP.Net site with a bunch of LinkButtons that all point to the same XLT-file on the server. The LinkButtons, when clicked, set some values in a table in a database, and then they open the XLT-file by using Response.TransmitFile. See code below. The XLT-file has some VBA macros in it (that I didn't write) which contact the same database the LinkButtons wrote to in order to retrieve the name of the table in the link that was clicked. Then the macros fill the Excel workbook with values from this table.

This is all fine and dandy, so far... The problem is when you attempt to open multiple instances, i.e. when you click on more links. The first time I click a link, I get the option to Open, Save or Cancel. I click Open, and Excel correctly opens, the macros kick in, and I see values. When I click a second link, a new window is supposed to open and values pertaining to the other link are supposed to display in it. Instead, the original Excel application is merely brought up and given focus, like if I'd clicked its button in the taskbar, and nothing happens. I see only the Workbook window with the values from the first link in it. I don't get a new option to Open, Save or Cancel, and I get no new Excel instance or Workbook window.

Interestingly, this is an IE thing. It works perfectly well in FireFox. Here, it asks me if I want to open or save the file every time I click a link, and if I select Open, it simply gives me multiple instances of the entire Excel application. To begin with, I thought this might've been because all the links point to the same XLT-file and Windows somehow detects that the file is already open when I click the second link, but this isn't the case. I put in a button that opens an XLT-file with a different name just to test it, and it had the same behavior.
Anybody know how I can fix this? I've already tried a lot of the solutions involving the addition of "%1" to the File Type advanced settings as well as disabling DDE in order to force new, seperate applications to open, and none of them work.

All help greatly appreciated!

Christian Pedersen

Code:

void OpenLink_Click(object sender, EventArgs e)
    {
        // Recover tablename of the linkbutton that was clicked
        string tablename = ((LinkButton)sender).ID;


*Do stuff here that writes data to the database that the Excel VBA Macros also access*




// Transmit the file to the user. I tried to use Response.Redirect to begin with, but that allowed the users to open the file remotely, and then the macros didn't work, so I had to use TransmitFile in order to force a download:
        System.IO.FileInfo TanarosTemplateFile = new System.IO.FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Tanaros.xlt");
        Response.Clear();
        Response.AddHeader("Content-Length", TanarosTemplateFile.Length.ToString());
        //Response.ContentType = "application/vnd.ms-excel";
        Response.ContentType = "application/msexcel";
        Response.AddHeader("Content-Disposition", "filename=Tanaros.xlt");
        Response.TransmitFile("Tanaros.xlt");
        Response.End();
    }

Re: Opening multiple Excel templates from ASP.Net

Here's a bit more information, if that helps:

Changing the line,

Response.AddHeader("Content-Disposition", "filename=Tanaros.xlt");

To:

Response.AddHeader("Content-Disposition", "attachment; filename=Tanaros.xlt");

Reenables the links after the first download, i.e. I can click and open multiple excel windows, however, it's the actual, raw template file (.xlt) that I'm downloading and opening when I do it that way. Not the Excel sheet that the template file is supposed to "turn into" when it opens normally. The template file is read-only, its macros disabled.

Any ideas?

Re: Opening multiple Excel templates from ASP.Net

This could be very unrelated, but if it helps... great

ive noticed that while trying to open multiple windows, and using the window.open function, if the "Name" is the same for both windows, it will not bring up the new window. try setting a new name each time, even just adding a counter variable to the end of the page's name...

its a long-shot, but sometimes the answer is suprisingly simple.

Re: Opening multiple Excel templates from ASP.Net

I thought the same you did, but appending the filename header with the same filename ("Tanaros.xlt") didn't cause a problem. Usually, it'll simply add a number in brackets to the opening file when given the filename of a window that's already open, i.e. if you click multiple times, you'd get Tanaros[1].xlt, Tanaros[2].xlt and so on.

I finally managed to solve the issue described above, however, by appending the header,

Response.AddHeader("Content-Disposition", "attachment; filename=Tanaros.xls");

Before the call to Response.TransmitFile. This causes XP to open the template-file (XLT) as a worksheet-file (XLS), and then the macros run and I can open multiple windows.

I now have another problem, though:

If I attempt to re-invoke the macros in Excel, Excel tells me the "File is locked for editing by [user-name]", where user-name is whoever's logged into the computer that opens the file. This, again, is a problem only in IE. FireFox doesn't have any of these issues whatsoever.

Re: Opening multiple Excel templates from ASP.Net

this (again) would be a suprisingly simple fix but being 20 yrs old and at my first programming job its all ive got for ya lol.

maybe try excel.worksheet.close and then re-open the file to run the macros again?

Re: Opening multiple Excel templates from ASP.Net

chickflickssuck

this (again) would be a suprisingly simple fix but being 20 yrs old and at my first programming job its all ive got for ya lol.

That's cool, any comment is greatly appreciated. I wish I'd known how to code when I was 20.

chickflickssuck

maybe try excel.worksheet.close and then re-open the file to run the macros again?

You're fairly close to the solution I came up with, actually. The Excel namespace in which worksheet.close (among other things) reside are found either with VBA code in the macros or with ActiveX-objects instantiated through JavaScript. After much toiling with this bugger, I ended up detecting which browser was being used in the C# code-behind (using Request.Browser.Browser), and then handling IE differently from any other browser. If the user's using IE, I call a JavaScript function that uses ActiveX to force a whole new Excel application to open every time the user clicks a link. If the user's using Firefox (or whatever other browser) then the C# code-behind transmits the file like the code in the first post of this thread.

I've tested this with the users and this seems to be the only working solution I can come up with. I wish IE wasn't this awfully cumbersome to work with. Just to add scorn to misery, this very post is being written in Firefox as we speak. I couldn't open the Reply-link in IE 8. It spawned Javascript exceptions when I tried.

Case closed.

/Christian H. Pedersen\

Re: Opening multiple Excel templates from ASP.Net

ChristianPedersen

Hey all!

First off, I apologize if this is the incorrect place to put this question. I was unsure where to stick it and selected the clientside section because in all honesty, this is a problem that's located on the client's computer. :)

I have a simple ASP.Net site with a bunch of LinkButtons that all point to the same XLT-file on the server. The LinkButtons, when clicked, set some values in a table in a database, and then they open the XLT-file by using Response.TransmitFile. See code below. The XLT-file has some VBA macros in it (that I didn't write) which contact the same database the LinkButtons wrote to in order to retrieve the name of the table in the link that was clicked. Then the macros fill the Excel workbook with values from this table.

This is all fine and dandy, so far... The problem is when you attempt to open multiple instances, i.e. when you click on more links. The first time I click a link, I get the option to Open, Save or Cancel. I click Open, and Excel correctly opens, the macros kick in, and I see values. When I click a second link, a new window is supposed to open and values pertaining to the other link are supposed to display in it. Instead, the original Excel application is merely brought up and given focus, like if I'd clicked its button in the taskbar, and nothing happens. I see only the Workbook window with the values from the first link in it. I don't get a new option to Open, Save or Cancel, and I get no new Excel instance or Workbook window.

Interestingly, this is an IE thing. It works perfectly well in FireFox. Here, it asks me if I want to open or save the file every time I click a link, and if I select Open, it simply gives me multiple instances of the entire Excel application. To begin with, I thought this might've been because all the links point to the same XLT-file and Windows somehow detects that the file is already open when I click the second link, but this isn't the case. I put in a button that opens an XLT-file with a different name just to test it, and it had the same behavior.
Anybody know how I can fix this? I've already tried a lot of the solutions involving the addition of "%1" to the File Type advanced settings as well as disabling DDE in order to force new, seperate applications to open, and none of them work.

All help greatly appreciated!

Christian Pedersen

Code:
void OpenLink_Click(object sender, EventArgs e)
    {
        // Recover tablename of the linkbutton that was clicked
        string tablename = ((LinkButton)sender).ID;

*Do stuff here that writes data to the database that the Excel VBA Macros also access*


// Transmit the file to the user. I tried to use Response.Redirect to begin with, but that allowed the users to open the file remotely, and then the macros didn't work, so I had to use TransmitFile in order to force a download:
        System.IO.FileInfo TanarosTemplateFile = new System.IO.FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Tanaros.xlt");
        Response.Clear();
        Response.AddHeader("Content-Length", TanarosTemplateFile.Length.ToString());
        //Response.ContentType = "application/vnd.ms-excel";
        Response.ContentType = "application/msexcel";
        Response.AddHeader("Content-Disposition", "filename=Tanaros.xlt");
        Response.TransmitFile("Tanaros.xlt");
        Response.End();
    }

Hey, i've just found this page while looking for how to open an excel template and add some data into it ,you said that it works for you, can you please send me the code you used?

best regards!