Hi,
We’ve got the following problem:
Our webapplicaiton creates an excel file which the user can download. When the user chooses the ‘open’ option in IE a runtime error occurs in teh vba code of the file. The error occurs because a reference to another cell in the samen workbook is invalid. When the user first saves the file to disk an then opens it, it works fine.
This is the code used to create and send the file to the client:
Excel excel = new Excel();
Stream designerStream = Assembly.GetExecutingAssembly().GetManifestResourceStream(“Denso.Iclaim.Business.Facade.ClaimReport.claimreport.xls”);
MemoryStream designerMemoryStream = new MemoryStream();
excel.Open(designerMemoryStream);
//FILL DATA IN THE FILE HERE
MemoryStream xlsStream = excel.SaveToStream(FileFormatType.Default);
//Clear the response and set up the response headers for downloading the file.
Response.Clear();
Response.AddHeader(“Content-Disposition”, “attachment; filename=ClaimReport.xls”);
// Set the HTTP MIME type of the output stream
Response.ContentType = “excel/ms-excel; name=ClaimReport.xls”;
//Convert the memorystream to an array of bytes.
byte[] byteArray = xlsStream.ToArray();
//Clean up the memory stream
xlsStream.Flush();
xlsStream.Close();
// Add a HTTP header to the output stream that contains the
// content length(File Size). This lets the browser know how much data is being transfered
Response.AddHeader(“Content-Length”, byteArray.Length.ToString());
// Write the data out to the client.
Response.BinaryWrite(byteArray);
//Close the respond
Response.End();
Can someone give me some pointer on what I am doing wrong, or can someone send me some example code of how they did it.
regards, Felix
Hi Felix,
Have you tried our latest hotfix v2.7.5
If the new hotfix still have this problem, please send me your created ClaimReport.xls. Thank you.
And could you try to set content type like this?
Response.ContentType = “application/vnd.ms-excel”;
Hi,
I’ve installed the new version and changed the ContentType , but no change. I get the same error.
I’ve found that the error is not in the VBA. My script loops through the Charts on the sheet, and refreshes the pivottables it is depending on:
-------------------
For Each ch In ActiveSheet.ChartObjects
ch.Activate
ActiveChart.ChartArea.Select
If Not ActiveChart.PivotLayout Is Nothing Then
ActiveChart.PivotLayout.PivotTable.RefreshTable
End If
Next
-------------------
On the line starting with “ActiveChart.PivotLayout” it gives an error: “Reference not Valid”.
This error however is not from the VBA, but from the pivottable. When I go to the pivottable itself and let is refresh (by clicking the red exclamation point in the pivottable toolbar) I get the same "reference is not valid error and also the error “Cannot open PivotTable source file ‘ClaimReport[1].xls’”. This file is the same file as the current open file. One of the sheets holds the source data for the pivottables.
When the file is saved to disk first, this all works fine.
I’ll mail the generated file to Laurence.
Hi Felix,
This problem is caused by the file name.
Your file name is “CalimReport[1].xls” and I also find your problem in it.
I rename your file to “CalimReport.xls” and all work fine. I think if a file name contains “[xx]”, the pivot table will fail to refresh.
Please have a try.
Yes, I figured that out too. The thing is, that I do not set the filename to be ClaimReport[1].xls, but I set it to ClaimReport.xls/
It seems that IE saves it with an additional [1]. Looking in my temporary internet folder, this is the case al lot of files.
Do you know of any way to make IE not rename the file to something witb [xx]?
Another way I could live with (and I hope my customer too) is to disable the ‘open’ button when the file arrives, but only allow the user to choose the Save option. Is this possible? And if so, how?
regards, Felix
Hi Felix,
I check but cannot find a way to make IE not rename the file or disable the “open” button.
Maybe you have to save the file on the server and provide a link for your users.
Hi,
Try redirecting the browser to the generated excel file. This will workout.
Rgds,
Nandeesh