6 Replies Latest reply on Feb 5, 2008 2:44 PM by Mahir Abrahim

    VBA Excel Problems

    Peter Zeyher
      Hi,

      I have written a macro that copys a BOM content from a drawing into an Excel BOM. It all works fine except for two problems:

      After all copying is done is use a dialog to ask the user to either view the Excel BOM or not.
      To view i just use

      appExcel.Visible = True

      since the makro runs excel "invisible".

      If the user chooses to not view the BOM I would like excel to quit. I am trying to achieve this with

      appExcel.Application.Quit

      but with no success. An invisible instance of excel stays open.

      How to I properly quit excel?

      The second problem is that the user chooses the Excel BOM from a open file dialog:

      fileToOpen = appExcel.GetOpenFilename("BOM_Template (*.xls), *.xls", , "Wählen Sie die Stücklistendatei aus in die gespeichert werden soll")

      If the user is not fast enough with locating a excel BOM the system seems to think that there is something wrong excel and a error message pops up letting the user choose to repeat or locate a different application (its in german and i don't know the proper translation).

      Is there any way to give the user more time to locate the correct excel BOM?

      Thanks already

      Peter
        • VBA Excel Problems
          Mahir Abrahim
          For quitting, I just use xlApp.Quit and it works fine using Excel 2003 and SW2007. Afterwards I clean up with Set xlApp = Nothing.

          As for the filename selection, I've never run into a time limit with GetOpenFilename. If I try to switch windows I might get a "Server Busy" messagebox that asks me to Switch To, Retry, or Cancel. Why don't you try GetSaveAsFilename instead. The only diffeence is it will let your user pick a filename that does not exist, which would require some extra error checking code.
            • VBA Excel Problems
              Peter Zeyher
              I just tried out both of your suggestions, unfortunately without success (excel 2000, SW2007).

              "Server Busy" is exactly the error I get and tried to describe in my initial post. What do you mean with "try to switch windows". I get the error while looking for the appropriate file in the getopenfilename. The same thing happens with getsaveasfilename but the "server busy" box stays in the background. This is a small step forward but still annoying.
            • VBA Excel Problems
              Mahir Abrahim
              When I say "switch windows" I mean taking focus away from your form to some other window. Maybe you have a background function or external process that is trying to assign focus away from your form. Since the Open File and Save File dialogs are modal, this would cause the "server busy" errors you're seeing.
              • VBA Excel Problems
                Peter Zeyher
                BTW I solved one of my problems (not properly quitting excel, the main sub was calling excel again after it was closed in a sub function) but the "server busy" error persists. Any suggestions on how I could start to figure out what is causing this?

                Peter
                • VBA Excel Problems
                  Mahir Abrahim
                  At least you fixed 1 out of 2. The server busy message comes up in many applications. From the little reading I've done about it, the cause is usually some external process or service that is using resources. Check your pc for spyware. Also, check if turning off your antivirus has any effect. Make sure you have the latest updates for your OS. I don't know if it avoids the server busy message or not, but you can also call the Open dialog using the Windows API instead of through Excel. Good luck.