Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Close Dialog Box Via Vba

  1. #1
    Join Date
    26th February 2007
    Posts
    8

    Close Dialog Box Via Vba

    Hello All

    Using VBA, I want to open an htm file via a Word document, edit the HTML script, save the changes then close it.

    I've tried all of the available file converters and number 3 ("Recover Text from Any File") is the only one capable of opening the htm file as text, but it always yields the dialog box "Show Repairs"

    I have tried 4 different methods of automatically closing this dialog box - none of them work

    1) Turn off all alerts, run script, turn on alerts

    Code:
    Application.DisplayAlerts= False
    'my script'
    Application.DisplayAlerts=True
    2) Key ALT+F4 to close the active window immediately after opening file

    Code:
    SendKeys "%{F4}"
    3) Key TAB 3 times then key ENTER to close the active window immediately after opening file

    Code:
    SendKeys "{TAB 3}"
    SendKeys "{ENTER}"
    4) Close the active window immediately after opening file

    Code:
    ActiveWindow.Close
    I've looked at
    Code:
    Dialogs(wdDialogShowRepairs)
    but you cannot declare "close" or "hide" with it.

    Here is my script in full. It works perfectly if a human is around to close the dialog box...

    Code:
    Sub Update_File()
        
        ChangeFileOpenDirectory _
        "\\server\folder\"
        Documents.Open FileName:="file.htm", ConfirmConversions:=False, _
            ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
            PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
            WritePasswordTemplate:="", Format:=FileConverters(3).OpenFormat, _
            Encoding:=1252 _
        Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .Text = "BODY BGCOLOR=#003399"
            .Replacement.Text = "BODY BGCOLOR=#FFFFFF"
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
        ActiveDocument.Save
        ActiveDocument.Close
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th February 2007
    Posts
    8

    Re: Close Dialog Box Via Vba

    Solved myself. You don't need to use a file converter - just declare format 4

    Code:
    ChangeFileOpenDirectory _
        "\\server\folder\"
        Documents.Open FileName:="file.htm", ConfirmConversions:=False, ReadOnly:= _
        False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _
        "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
        Format:=4

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    Re: Close Dialog Box Via Vba

    Hey thanks for sharing your solution.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. VBA Project Password Dialog Shows On Close
    By iwrk4dedpr in forum EXCEL HELP
    Replies: 11
    Last Post: September 18th, 2007, 21:25
  2. Close Find Dialog After Value Found
    By SerenityNetwork in forum EXCEL HELP
    Replies: 7
    Last Post: July 13th, 2007, 00:38
  3. Replies: 4
    Last Post: October 8th, 2005, 10:49
  4. Replies: 3
    Last Post: August 24th, 2005, 21:09
  5. VBA to close Excel Window on close last workbook
    By Dovrox in forum EXCEL HELP
    Replies: 2
    Last Post: September 22nd, 2004, 19:06

Bookmarks

Posting Permissions

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