+ Reply to Thread
Results 1 to 2 of 2

Importing Word Table

Hybrid 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.

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

    Re: Importing Word Table

    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

+ Reply to Thread

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