none
ASP.Net can't open xlsm files, if they contain VBA code.

    Question


  • Hi everyone,

    I have a nasty problem.

    My ASP.Net application running on an IIS server, which includes some code to read in an existing Excel 2007 ".xlsm" file, and save it elsewhere.

    Let's keep things simple.   I can create a brand new .xlsm file, perhaps put a few numbers in some of the cells, save it, and my ASP.Net code will happily work perfectly.  This following command will happily load in this file, and I can use SaveAs to save it elsewhere:

    xlApp.Workbooks.Open(templatePath, 0, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

    Now, if I take the same .xlsm file, and the following pointless VBA to it...
    Public Sub Workbook_Activate()
      Dim n As Integer
      n = 3
    End Sub
    ... then when I run my app, the Workbooks.Open command throws an exception, and refuses to open this file:

    Exception from HRESULT: 0x800A03EC


    But..   If I take the same .xlsm file and ASP.Net application and run it locally, rather than on an external web server, it loads the .xlsm file without any problems.

    I don't understand this.

    I don't actually want to run the VBA code, I just want to load the Excel file, and save it straight back again to a different location, with no changes  (Yes, I know I can do a File.Copy, but bare with me..)

    On the web server, I have tried adding the path to Excel's "Trusted Locations", and ticking "Enable all Macros" in the "Trust Centre", but nothing seems to help.

    I also tried adding a StreamWriter immediately after this code, to write a txt file into the same target directory  (to check the user's read/write permissions) on the web server - this runs fine.

    Any ideas ?
    Do I have to sign my .xlsm files, or something ?


    Btw, this tiny - but essential - bug is part of a larger problem.  The ASP.Net code actually then goes on to load in this Excel file, set a few "Name" values, set the contents of some cells, etc, and then save it back.   When this ASP.Net app is run locally, it works fine.   But when it's run remotely, as in the example above, I can't even get the first part to work.



     

    Friday, February 11, 2011 3:27 PM

Answers

  • Got it.  

    Locally, I'm running Excel as myself, so any Trust Center changes I make directly affect me.

    But on my web server, I'm logging in as me, changing the Trust Center settings, so it affects my settings on that server.   But my ASP.Net application is actually running as an Application Pool User instead.    So when the Pool User tries to open my xlsm file, it fails miserably, as I haven't given it permissions to open such files.

    My solution  (as I don't have our company's Pool User login details)  was to open the xlsm file, hit CTRL+F11 to go into the VBA, Tools\Digital Signatures, then attach a Certificate to my .xlsm file.

    Now, the pool user can also happily open the .xlsm file.

    (Long, exhausted sigh.)

     

    Hope this helps someone else !

     

    • Marked as answer by Bruce Song Monday, February 14, 2011 6:45 AM
    Friday, February 11, 2011 4:04 PM
  • Just one last comment.

    Before doing all this .xlsm investigation, my Excel files, containing the VBA code, were all "Excel 2003" .xls files.

    And, oddly, they seemed to work throughout the ASP.Net until they reached the SaveAs command in my code.  At that point, it'd throw an exception of:

    Document not saved.

    So, if anyone's having ASP.Net web-server problems, resulting in that exception...  check which user you're running as, and if they have permission to read/write your Excel files.

     

    • Marked as answer by Bruce Song Monday, February 14, 2011 6:46 AM
    Friday, February 11, 2011 4:10 PM

All replies

  • Got it.  

    Locally, I'm running Excel as myself, so any Trust Center changes I make directly affect me.

    But on my web server, I'm logging in as me, changing the Trust Center settings, so it affects my settings on that server.   But my ASP.Net application is actually running as an Application Pool User instead.    So when the Pool User tries to open my xlsm file, it fails miserably, as I haven't given it permissions to open such files.

    My solution  (as I don't have our company's Pool User login details)  was to open the xlsm file, hit CTRL+F11 to go into the VBA, Tools\Digital Signatures, then attach a Certificate to my .xlsm file.

    Now, the pool user can also happily open the .xlsm file.

    (Long, exhausted sigh.)

     

    Hope this helps someone else !

     

    • Marked as answer by Bruce Song Monday, February 14, 2011 6:45 AM
    Friday, February 11, 2011 4:04 PM
  • Just one last comment.

    Before doing all this .xlsm investigation, my Excel files, containing the VBA code, were all "Excel 2003" .xls files.

    And, oddly, they seemed to work throughout the ASP.Net until they reached the SaveAs command in my code.  At that point, it'd throw an exception of:

    Document not saved.

    So, if anyone's having ASP.Net web-server problems, resulting in that exception...  check which user you're running as, and if they have permission to read/write your Excel files.

     

    • Marked as answer by Bruce Song Monday, February 14, 2011 6:46 AM
    Friday, February 11, 2011 4:10 PM
  • Hi Mike,

    Thank you for posting and we are glad to hear that you have resolved your issue yourself. And thank you for sharing your solutions & experience here. It will be very beneficial for other community members who have similar questions. Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Bruce Song Monday, February 14, 2011 6:45 AM typo
    Monday, February 14, 2011 6:45 AM
  • Hi Mike,

    I have the same problems that you, can you tell me please, how can I create a certificate?

     

    thanks in advance, regards


    Monday, April 11, 2011 7:34 PM
  • > I have the same problems that you, can you tell me please, how can I create a certificate?

    You need to buy a code-signing certifcate.

    The company I work for had it's own certificates in place, and personally, I have my own certificates, 
    from www.thawte.com 

    They charge for code-signing per year, rather than per signing.  
    But I've had some pretty rubbish customer service from them.

     

    Tuesday, May 03, 2011 9:20 AM