PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] [Excel 2010] Automation Error when creating SaveAs file dialog-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] [Excel 2010] Automation Error when creating SaveAs file dialog

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    8

    Resolved [RESOLVED] [Excel 2010] Automation Error when creating SaveAs file dialog

    I have many macros in my personal workbook (PERSONAL.XLSB), and so when I close all open workbooks the Excel application window stays open and I can still use the Visual Basic editor to edit these macros. I have narrowed my problem down to this:

    Code:
    Sub Tester()
        Dim MyDialog As FileDialog
        Set MyDialog = Application.FileDialog(msoFileDialogSaveAs)
    End Sub
    When I execute this code with a workbook open (like the default Book1), this works fine. If I close all workbooks and execute this code, then I get the following error:

    Automation error
    Exception occurred.

    Then the windows error reporting system chimes in with "Microsoft Excel has encountered a problem and needs to close." and the application closes. I can post the details of the error report if it would help, but otherwise I'll spare you.

    The error does not occur with the other three types of file dialog (FilePicker, FolderPicker, Open).

    I want to use this as part of a larger macro that will create a new workbook with certain parameters that I specify, and I need the SaveAs dialog to be able to create a new file. I'd like it to run regardless of whether other workbooks are open. Any ideas?

  2. #2
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: [Excel 2010] Automation Error when creating SaveAs file dialog

    The problem is probably because you are telling it to save something when there's nothing there. If you need the save as dialog to show up, why not create a template with your parameters, then copy it as...
    Or even just open a new workbook first within your macro, then add the changes. The new workbook is never really saved, so when you simply save it you will get the save as dialog anyways, it will just be after the changes, not before.

  3. #3
    Addicted Member
    Join Date
    Jan 2009
    Posts
    183

    Re: [Excel 2010] Automation Error when creating SaveAs file dialog

    You can't save a file if there is no active document to save.

    Sub Tester()
    Dim MyDialog As FileDialog
    If Application.Workbooks.Count > 0 Then
    Set MyDialog = Application.FileDialog(msoFileDialogSaveAs)
    End If
    End Sub

    or if you want it to create a new document, then put in an 'Else' clause with the code you want.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    8

    Re: [Excel 2010] Automation Error when creating SaveAs file dialog

    Thank you both for the replies. I was able to fix it using by using a template to house the basic workbook format and open that before bringing up the SaveAs dialog. The logic check before doing so helps catch any errors.

    Any idea why bringing up the file dialog without an open workbook would cause a fatal error instead of one caught by the usual debugger?

  5. #5
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: [RESOLVED] [Excel 2010] Automation Error when creating SaveAs file dialog

    I am glad you got it working!

    Any idea why bringing up the file dialog without an open workbook would cause a fatal error instead of one caught by the usual debugger?
    The debugger catches errors in the portion (Sub or Function) of your code that it is currently running through. If that portion of the code refers to another Function or Sub, and there is an error in that function it will not give you a normal debugging error.
    Technically there is not really a problem with your code, just how you used it. When you call
    vb Code:
    1. Application.FileDialog
    that is actually running a code that is built into the applcation, which is giving an error because you are attempting to do a save when nothing exists.
    Last edited by nO_OnE; Apr 27th, 2011 at 06:53 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width