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?
Bookmarks