none
Extremely rare SaveAs Prompt in Excel 2007 (random FileName) with Automation (VB.NET) RRS feed

  • Question

  • I'm automating Excel 2007 with VB.NET on Server 2008 (64Bit) and have a rare problem during the call of Workbook.SaveAs(..). A prompt (with a random Filename) is displayed and this breaks the Automation till someone clicks on Save or Cancel.

    I'm saving to Excel.xlFormat12 (*.xlsb) without overwrite (i'm deleting the older file if one is found with System.IO.File.Delete).

    I believe the problem may be linked to Excel creating a temporary file during the save Operation and "maybe" reaching an internal Limit (.NET does have a limit of 65535 calls on "GetTempFilename" .. if the file is not deleted).

    Currently i'm running a simple open/delete/save loop on my windows7 machine to try to reproduce the Problem because i don't have a Server 2008 System available (no error till now).

    Is there some workaround for this? Maybe a way to block Excel from creating temporary files?

    Monday, November 3, 2014 3:51 PM

Answers

  • I could reproduce the Error...

    I started a second program which renames the Folder back and forth (every 250ms). It took 5 minutes for my mainprogram to stop with a prompt (Saving "random" Filename). Maybe there are other possibilities to force this error (like removing the write access for the user for a short time).

    Either way, the Error now seems unrelated to Excel so i will look how to make sure my destination folders are ok.

    • Marked as answer by PTal Tuesday, November 11, 2014 10:12 AM
    Tuesday, November 11, 2014 10:11 AM

All replies

  • Did you set DisplayAlerts to false?

    ExcelApp.DisplayAlerts = False

    Monday, November 3, 2014 4:48 PM
  • Hello PTal,

    > I'm automating Excel 2007 with VB.NET on Server 2008 (64Bit)

    Where and how do you automate Excel? Could you please be more specific? Is it a service or ASP.NET application?

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    See Considerations for server-side Automation of Office for more information.

    Tuesday, November 4, 2014 7:12 AM
  • Ok, i'll try to be more specific.

    The application is a VB.NET Windows.Forms Application running on a Server 2008 (user is logged in). It runs mainly unattended, looks for production data, formats the data into a Excel template and saves a new workbook for each production lot it finds . The created workbooks (in a shared Folder) are opened from time to time (over the Network) by a Operator to check production data.

    So if no new Workbooks are available, after one/two days someone gets the idea to look at my program and see if it is still running (and in this case, most of the time the Screen is locked and they forgot the Password).

    The program is originally from 2004 (VB6), was rewritten sometime later to be in VB.NET and for the last year uses Excel 2007 (Prior to it, it was Excel 2003).

    I found somewhere info on the Internet that Excel does not create temporary Files if saving in the old XLS Format. But currently thats not an Option i want to consider.

    @Deric: yes, DisplayAlerts is false. I do not have a counter programmed in but a rough estimate tells me that the application works correctly for at least 5000 cycles (open Workbook, fill data, save Workbook) and then stops once with the prompt. As far as i know, cancelling the promt lets the application run again for days without a problem till the prompt Shows up again.

    Currently i'm considering checking my working thread to see if it takes too long and killing it (along with any open Excel application). I'll probably add a variable telling me if the thread is currently waiting for Workbook.SaveAs to complete.

    Thanks for the Answers



    • Edited by PTal Tuesday, November 4, 2014 7:51 AM
    Tuesday, November 4, 2014 7:38 AM
  • Hi PTal,

    I am trying to reproduce the issue but failed. Here is the test code:

      ActiveWorkbook.SaveAs Filename:="C:\Users\UserName\Desktop\Book1.xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False

    And based on the desciption, it seem that the issue not occure when you save the first workbook and the message box displayed after the application running for a while.

    If understand correctly, could this issue be reproduced on the other Operater System for the client version? For exmaple, Windows 7 or Windows 8 etc?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 5:36 AM
    Moderator
  • Hello Fei Xue,

    i've also tried (unsuccessfully) to reproduce the error on a Win7 System. I had to stop after 40k cycles because i needed the used win7 machine for something else ^^. No popup till then. It takes some time because for each cycle i create a new instance of Excel/Workbook and close Excel after each SaveAs (i also "wait" for Garbage Collection).

    GC.Collect()
    GC.WaitForPendingFinalizers()
    If Not wb Is Nothing Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb) : wb = Nothing
    If Not appEX Is Nothing Then appEX.Quit() : System.Runtime.InteropServices.Marshal.FinalReleaseComObject(appEX) : appEX = Nothing

    Currently i created a "Workaround" in the program (currently waiting for Feedback). Killing any open Excel Instances if the thread stopped during "SaveAs.." for longer than 5minutes and also restarting my application every 24h. Restarting the whole Server would be even better but is not possible :D

    Maybe it will be possible to start my test this weekend again to see if i can reach the 65k cycles on Win7 (if the error is related).

    • Edited by PTal Friday, November 7, 2014 5:14 AM
    Friday, November 7, 2014 4:47 AM
  • Hi PTal,

    Thanks for your detail information about this issue.

    >> Maybe a way to block Excel from creating temporary files?
    As far as I know, we could not prevent Excel to create temporary file.

    >> .NET does have a limit of 65535 calls on "GetTempFilename" .. if the file is not deleted
    The limit is from the Windows API rather than .NET.
    GetTempFileName function

    Since this issue could not be reproduced, I suggest that you use some log to troubleshoot this issue.
    Another way could try is releasing the Excel Application Object every once in a while. For example, after opening/saving 1000 documents, close the application and restart again.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 10, 2014 8:58 AM
    Moderator
  • Hi Fei,

    i'm already releasing the Excel Application Object after each cycle.

    Another Update:

    - I cannot reproduce the Error on a Win7 machine (no problems after +100k Cycles).

    - My Workaround (restarting application after ~24h, killing Excel process if application stops) is not working. This time i did not get the Dialog but directly the error message below

    System.Runtime.InteropServices.COMException at Microsoft.Office.Interop.Excel.WorkbookClass.SaveAs....
    
    Microsoft Office Excel cannot access the file 'D:\Data1\{Randomname without Extension}'.
    
    There are several possible reasons:
    The file name or path does not exist.
    The file is being used by another program.
    The workbook you are trying to save had the same name as a currently
    open workbook.

    The program cannot recover from this and the error shows up for each cycle from this point onwards (every cycle with a new random Name).

    And now it gets interesting. My program handles four "data" Folders one after another (same thread, the Folder with the error is processed first). The other three are always processed OK even after this error (Workbook.SaveAs works) and only stop because the whole program crashes 26h and ~160 ComExceptions later during the next scheduled Restart (Application.Restart).

    Now i'm thinking that there is something wrong with the Folder 'D:\Data1'.

    • Edited by PTal Tuesday, November 11, 2014 9:00 AM
    Tuesday, November 11, 2014 8:08 AM
  • I could reproduce the Error...

    I started a second program which renames the Folder back and forth (every 250ms). It took 5 minutes for my mainprogram to stop with a prompt (Saving "random" Filename). Maybe there are other possibilities to force this error (like removing the write access for the user for a short time).

    Either way, the Error now seems unrelated to Excel so i will look how to make sure my destination folders are ok.

    • Marked as answer by PTal Tuesday, November 11, 2014 10:12 AM
    Tuesday, November 11, 2014 10:11 AM