This problem is "kind-of" fixed now that I have seperated two methods in two different procedures. I say kind-of because its very rarely that Normal.dot file will be currupted. But i put up procedure for users to follow on how to fix Normal.dot. So fingers-crossed and nothing bad will happen. Still probably not efficient, but gets the job done
Below is the code I used for your reference:
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Sub ImportCableList()
Application.EnableEvents = False
Application.ScreenUpdating = False
Call disableProtection(ActiveSheet)
Dim wdFileName As Variant
'get filename and path
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Dim strDoc As String
strDoc = CStr(wdFileName)
wdFileName = False
'set table number to import. If invalid number found, set to defualt value of 2
On Error Resume Next
Dim tableNum As Integer
tableNum = 2
tableNum = ThisWorkbook.ActiveSheet.Range("M2").Value
On Error GoTo 0
'clear cells
Range("B4:I65536").Clear
Rows("4:65536").Font.ColorIndex = 1
'set cell where cable list will be pasted
Dim pasteRange As Range
Set pasteRange = ThisWorkbook.ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0)
'set number of tables in selected word file
Dim numTables As Integer
'Method1 is more efficient method of importing a word table than Method2
'However, Method1 might experience "Error 28: Out of Stack Space" error,
'which I have not found a solution on fixing it, yet. In the meantime, I have included
'another method (Method2) of performing the same task.
On Error Resume Next
If IsMethod1Err(strDoc, tableNum, pasteRange) Then Call Method2(strDoc, tableNum, pasteRange)
GoTo Exit_Procedure
DisplayError:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportWordTable of module ImportfromWord", vbExclamation
GoTo Exit_Procedure
Exit_Procedure:
With ThisWorkbook.ActiveSheet
'turn ON wraptext
.Columns("B:I").WrapText = True
'autofit rows
.Rows.AutoFit
'add borders
With Range("B4:I65536").Borders
.ColorIndex = 1
.LineStyle = xlContinuous
.Weight = xlThin
End With
'delete header row
.Range("B4").Select
Selection.EntireRow.Delete
End With
'update table number value
ThisWorkbook.ActiveSheet.Range("M2").Value = tableNum
Call disableProtection(ActiveSheet)
ActiveSheet.Cells.Locked = False
'clear clipboard
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Function IsMethod1Err(strDoc As String, tableNum As Integer, pasteRange As Range) As Boolean
IsMethod1Err = False
On Error GoTo exitFunction
'get handle on the file
Dim wordTable As Object
Set wordTable = GetObject(strDoc)
'ERROR CHECK
numTables = wordTable.Tables.Count
'update total number of tables
ThisWorkbook.ActiveSheet.Range("M3").Value = numTables
If numTables = 0 Then
MsgBox "There are no tables to import!", vbCritical
Exit Function
End If
If tableNum > numTables Then
MsgBox "Invalid table number." & vbNewLine & _
"The table number you specified is more than total number of tables in the Word Document", vbCritical
Exit Function
End If
'copy the table
wordTable.Tables(tableNum).Range.Copy
'paste the table to cable list
ThisWorkbook.ActiveSheet.Paste pasteRange
'destroy the object
Set wordTable = Nothing
' Range("N2").Value = "Method1 used"
IsMethod1Err = False
Exit Function
exitFunction:
IsMethod1Err = True
End Function
Sub Method2(strDoc As String, tableNum As Integer, pasteRange As Range)
On Error GoTo endProc
'requires a reference to Word Object Library (Tools - References) "Microsoft Word 11.0 Object Library"
Dim appWord As Word.Application
Dim docWord As Word.Document
'set Word Application
On Error Resume Next
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then Set appWord = CreateObject("Word.Application")
On Error GoTo endProc
' appWord.Visible = True
Set docWord = appWord.Documents.Open(strDoc)
'ERROR CHECK
numTables = docWord.Tables().Count
ThisWorkbook.ActiveSheet.Range("M3").Value = numTables
If numTables = 0 Then
MsgBox "There are no tables to import!", vbCritical
Exit Sub
End If
If tableNum > numTables Then
MsgBox "Invalid table number." & vbNewLine & _
"The table number you specified is more than total number of tables in the Word Document", vbCritical
Exit Sub
End If
'copy first table in document to clipboard
docWord.Tables(tableNum).Range.Copy 'err 5941 if tablenum>max
'paste table on worksheet
ThisWorkbook.ActiveSheet.Paste Destination:=pasteRange
docWord.Close False 'closes document without save
appWord.Quit 'closes word instance
If Not (docWord Is Nothing) Then Set docWord = Nothing
If Not (appWord Is Nothing) Then Set appWord = Nothing
' Range("N2").Value = "Method2 used"
Exit Sub
endProc:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Method2 of module ImportFromWord", vbExclamation
End Sub
Bookmarks