OK, After a week of thorough research I found a second method of importing a word table, since the first efficient method gave me out of stack error, which I still cant find a solution to. See below thread link to see solutions I have tried for out-of-stack error.
http://www.excelforum.com/excel-prog...ack-space.html
So I now use both methods, since most computers dont have error on 1st method. If they do, however, the program will use method2 and continue importing word table. I wanted to seek experts advice on this.
'need these to later clear large data on clipboard
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
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 'this field specifies the table number to import
On Error GoTo 0
'Method1 is more efficient method of importing a word table (thanx to snb) than Method2
'However, Method1 might experience "Error 28: Out of Stack Space" error,
'which I have not found a solution on fixing it. Therefore, I have included
'another method (Method2) of performing the same task.
'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)
On Error GoTo Method2
Method1:
'get handle on the file
Dim wordTable As Object
Set wordTable = GetObject(strDoc)
If tableNum > wordTable.Tables.Count Then tableNum = 2
'specify total number of tables
ThisWorkbook.ActiveSheet.Range("M3").Value = wordTable.Tables.Count
'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"
'if VBE execution reached this point, that means it was error free and need to exit procedure now, else use Method2
GoTo Exit_Procedure
Method2:
On Error GoTo DisplayError
'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 object
Set appWord = New Word.Application
' appWord.Visible = True 'i dont want to open the word document, paste table, then close it again
Set docWord = appWord.Documents.Open(strDoc)
If docWord.Tables().Count = 0 Then
MsgBox "There are no table to import!"
GoTo Exit_Procedure
End If
If tableNum > docWord.Tables().Count Then tableNum = 2
'specify total number of tables
ThisWorkbook.ActiveSheet.Range("M3").Value = docWord.Tables().Count
'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
appWord.Quit
Range("N2").Value = "Method2 used"
GoTo Exit_Procedure
DisplayError:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Method2 of procedure ImportWordTable of module Module3", vbExclamation
GoTo Exit_Procedure
Exit_Procedure:
If Not (docWord Is Nothing) Then Set docWord = Nothing
If Not (appWord Is Nothing) Then Set appWord = Nothing
'update table number value
ThisWorkbook.ActiveSheet.Range("M2").Value = tableNum
'clear clipboard
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
If Method2 is used, then lot of user prompts are opened, such as open read-only, save normal.dot, ok button, .... Obvisouly, that's very inefficient. any chance i can fix this procedure? (sorry if its too long to read)
Bookmarks