+ Reply to Thread
Results 1 to 8 of 8

Can't disable Word Alerts

Hybrid View

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Can't disable Word Alerts

    I have a macro that I'm using for extracting data from a word document, however, Word interrupts the process with conversion confirmation dialogue boxes and other alerts. I would like to turn of all word alerts while this macro is running.

    Here's my code:

    Option Explicit
    
    Sub ImportWordTable()
    Dim wdDoc As Object
    Dim wdFileName As Variant
    Dim TableNo As Integer 'table number in Word
    Dim desigTableNo As Integer 'designated table that we want to import from
    Dim iRow As Long 'row index in Excel
    Dim iCol As Integer 'column index in Excel
    Dim lRow As Integer 'current row in loop
    Dim lLR As Long 'last row of data in excel sheet
    Dim wRow As Integer 'row of word table we want data from
    Dim wCol As Integer 'column of word table we want data from
    Dim pCol As Integer 'column we want to past info into
    
    
    'find number of rows in spreadsheet
    lLR = Range("A" & Rows.Count).End(xlUp).Row
    'set which table we want to pull data from
    desigTableNo = Sheets("Search").Cells(2, 3).Value
    'set which table row
    wRow = Sheets("Search").Cells(3, 3).Value
    'set which table column
    wCol = Sheets("Search").Cells(4, 3).Value
    'set which column we want info to be pasted to
    pCol = Sheets("Search").Cells(7, 3).Value
    
    'Code optimizer for much quicker cycle time (see module 1)
    Call OptimizeCode_Begin
    
    For lRow = 26700 To lLR
    
    wdFileName = Cells(lRow, 1)
    On Error Resume Next
    wdDoc.Visible = False
    
    Set wdDoc = GetObject(wdFileName)
    wdDoc.Visible = False
    
    With wdDoc
        TableNo = wdDoc.Sections(1).Headers(1).Range.tables.Count
        If TableNo = 0 Then
            Cells(lRow, pCol) = "No Tables!"
            On Error Resume Next
        ElseIf TableNo > 1 Then
            TableNo = desigTableNo
        End If
        With .Sections(1).Headers(1).Range.tables(desigTableNo)
            'copy cell contents from Word table cells to Excel cells
            For iRow = lRow To lRow
                    Cells(iRow, pCol) = WorksheetFunction.Clean(.cell(wRow, wCol).Range.Text)
            Next iRow
        End With
        Application.DisplayAlerts = True
    End With
    
    wdDoc.ActiveDocument.Close (False)
    wdDoc.Quit
    Set wdDoc = Nothing
    
    Next lRow
    
    'Code optimizer for much quicker cycle time (see module 1)
    Call OptimizeCode_End
    
    End Sub
    Ideas?

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Can't disable Word Alerts

    There should be few times that you will need that. It is commonly used when doing a mail merge.

    Try using early binding to make sure that you are using proper syntax at compile time rather than run time. You can easily change it back to late binding in the production version.

    Note how I used constant values rather than constant variables for the late binding method.
    Sub Main()
      Dim wdDoc As Object  'Late Binding
      'Dim wdDoc As Word.Document 'Early Binding, add Word object to references.
      Dim wdFileName As String
      
      wdFileName = "C:\Users\lenovo1\Dropbox\Excel\MSWord\ElephantsRusDocs.doc"
      Set wdDoc = GetObject(wdFileName)
      wdDoc.Application.Visible = False
      'wdDoc.Visible = False
      'Debug.Print wdDoc.Content
      wdDoc.Application.DisplayAlerts = 0 'wdAlertsNone
      wdDoc.Application.DisplayAlerts = -1 'wdAlertsAll
      wdDoc.Close
      Set wdDoc = Nothing
    End Sub
    Last edited by Kenneth Hobson; 09-11-2017 at 05:01 PM.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Can't disable Word Alerts

    OK, I've updated my code with what I THINK is early binding, but I'm still getting alert messages. Here's my updated code:

    Option Explicit
    
    Sub ImportWordTable()
    'Dim wdDoc As Object
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdFileName As String
    Dim TableNo As Integer 'table number in Word
    Dim desigTableNo As Integer 'designated table that we want to import from
    Dim iRow As Long 'row index in Excel
    Dim iCol As Integer 'column index in Excel
    Dim lRow As Integer 'current row in cycle
    Dim lLR As Long 'last row of data in excel sheet
    Dim wRow As Integer 'row of word table we want data from
    Dim wCol As Integer 'column of word table we want data from
    Dim pCol As Integer 'column we want to past info into
    Dim count As Integer
    Dim per As Long
    count = 0
    
    
    'find number of rows in spreadsheet
    lLR = Range("A" & Rows.count).End(xlUp).Row
    'set which table we want to pull data from
    desigTableNo = Sheets("Search").Cells(2, 3).Value
    'set which table row
    wRow = Sheets("Search").Cells(3, 3).Value
    'set which table column
    wCol = Sheets("Search").Cells(4, 3).Value
    'set which column we want info to be pasted to
    pCol = Sheets("Search").Cells(7, 3).Value
    
    'Code optimizer for much quicker cycle time (see module 1)
    Call OptimizeCode_Begin
    Application.DisplayAlerts = False
    
    
    For lRow = 700 To lLR
    
    wdFileName = Cells(lRow, 1)
    On Error Resume Next
    wdDoc.Visible = False
    wdApp.Application.DisplayAlerts = 0 'wdAlertsNone
    wdApp.Application.Options.ConfirmConversions = False
    
    Set wdDoc = GetObject(wdFileName)
    wdDoc.Visible = False
    
    With wdDoc
        TableNo = wdDoc.Sections(1).Headers(1).Range.tables.count
        If TableNo = 0 Then
            Cells(lRow, pCol) = "No Tables!"
            On Error Resume Next
        ElseIf TableNo > 1 Then
            TableNo = desigTableNo
        End If
        With .Sections(1).Headers(1).Range.tables(desigTableNo)
            'copy cell contents from Word table cells to Excel cells
            For iRow = lRow To lRow
                    Cells(iRow, pCol) = WorksheetFunction.Clean(.cell(wRow, wCol).Range.Text)
            Next iRow
        End With
        'wdDoc.Application.DisplayAlerts = -1 'wdAlertsAll
    End With
    wdDoc.ActiveDocument.Close (False)
    wdDoc.Quit
    Set wdDoc = Nothing
    
    'progress counter
    count = count + 1
    per = count / lLR * 100
    Excel.Application.StatusBar = count & "/" & lLR & " " & per & "%"
    
    Next lRow
    Application.DisplayAlerts = True
    'Code optimizer for much quicker cycle time (see module 1)
    Call OptimizeCode_End
    
    End Sub

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Can't disable Word Alerts

    You did not set a wdApp object. Referencing it therefore errors. I showed you how to get to the MSWord Application object from the MSWord document, wdDoc. IF you don't want to reference the MSWord Application object as I did, you can do it with:
    Set wdApp =  wdDoc.Application
    To fix the lines in red and probably your MSWord alerts, change wdApp to wdDoc.

    I normally set wdApp before setting the document. I like to get the reference to an existing wdAdd or add a new instance. Then I can delete a created instance if needed.

    You should get an error for wdDoc.Visible as well. That is an MSWord Application command. Compile should have shown the Visible issue. I like to Compile from the Debug menu before a Run.

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Can't disable Word Alerts

    OK, I've broken this down and I'm testing with this code below, but I'm still getting alert messages. I'm not getting any errors.

    Sub opendoc()
    Dim wdDoc As Word.Document
    Dim wdFileName As String
    
    wdFileName = "...filepath..."
    Set wdDoc = GetObject(wdFileName)
      wdDoc.Application.Visible = False
      wdDoc.Application.DisplayAlerts = 0
      wdDoc.Application.Options.ConfirmConversions = False
      wdDoc.Close
    End Sub

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Can't disable Word Alerts

    Hi,

    You're disabling in the wrong place. Use
    For lRow = 700 To lLR
    
    wdFileName = Cells(lRow, 1)
    On Error Resume Next
    
    Set wdDoc = GetObject(wdFileName)
    wdDoc.Visible = False
    with wdDoc.Application
       .DisplayAlerts = 0 'wdAlertsNone
       .Options.ConfirmConversions = False
    end with
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Can't disable Word Alerts

    I think it would be better to open an application first, keep that open, and then open each document
    Sub opendoc()
    Dim wdApp as Word.Application
    Dim wdDoc As Word.Document
    Dim wdFileName As String
    
    wdFileName = "...filepath..."
    Set wdApp = CreateObject("Word.Application")
      wdApp.Visible = False
      wdApp.DisplayAlerts = 0
      wdApp.Options.ConfirmConversions = False
       set wdDoc = wdApp.Documents.Open(wdFileName)
      wdDoc.Close
    End Sub

  8. #8
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Can't disable Word Alerts

    That's working great now guys! Thanks for getting me going in the right direction!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to disable application alerts On workbook opening!
    By Korjaaja in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-08-2014, 12:11 PM
  2. Disable word from trying to load links
    By koticphreak in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2013, 05:52 PM
  3. Replies: 1
    Last Post: 06-16-2013, 12:35 AM
  4. How to disable track changes in Word 2007
    By sugaprasad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2013, 12:47 PM
  5. Replies: 0
    Last Post: 08-15-2012, 08:13 AM
  6. Disable AutoMacros in Word Doc and delete all text files
    By abousetta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2010, 03:03 AM
  7. Disable all kinds of messages/warnings/alerts in Excel?
    By hraza in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-06-2009, 04:17 AM
  8. Replies: 16
    Last Post: 12-17-2005, 11:40 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1