Results 1 to 2 of 2

Importing Word Table

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-14-2010
    Location
    Toronto,Canada
    MS-Off Ver
    Excel 2003
    Posts
    145

    Importing Word Table

    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)
    Last edited by adds007; 03-03-2011 at 01:55 PM.

Thread Information

Users Browsing this Thread

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

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