SYDI script, Exporting/copying data from MS word to Excel sheet using VBA

wow it has been a while since I blogged.

I did something interesting this week and I wanted to share it with you.

This week, I got a task to check 400 servers for their startup parameters for security, now I though, I won’t log into each server and do it manually, I am so lazy for this.

SYDI script has a nice feature to export the server info along with the startup parameters, so I did SYDI commands and I exported the 400 servers data, but now I have 400 documents, again, I am so lazy for this, I want a single sheet to read.

So, it is time for some VBA scripting, after some search and copying some scripts, I built this nice script, I thought about sharing it.

The script will look in the current document, search for the latest table which should be the startup parameters, copy the word table using VBA, select the first line in the document which should be the server name, open excel sheet, lookup the written rows, and paste the table at the latest one.

Note: I named the macro AutoOpen to start when opening the documents, I built another script to loop through server names, open the documents and I am done.

now I can have a single sheet to read during drinking my coffee.

Reference: http://stackoverflow.com/questions/15789505/vba-pull-data-from-ms-word-table-into-ms-excel-worksheet-not-special-paste

Enjoy

Sub AutoOpen()
    Dim wrdTbl As Table
    Dim RowCount As Long, ColCount As Long, i As Long, j As Long

    ‘~~> Excel Objects
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object
   
        Selection.MoveEnd Unit:=wdLine, Count:=1
Selection.Expand wdLine
hostname = Selection.Text

    tablecount = ActiveDocument.Tables.Count

      Set wrdTbl = ActiveDocument.Tables(tablecount)
 
               
      ColCount = wrdTbl.Columns.Count
    RowCount = wrdTbl.Rows.Count

    
    ‘~~> Set your table

    ‘~~> Get the word table Row and Column Counts
   
    ‘~~> Create a new Excel Applicaiton
    Set oXLApp = CreateObject("Excel.Application")

    ‘~~> Hide Excel
    oXLApp.Visible = False

    ‘~~> Open the relevant Excel file
    Set oXLwb = oXLApp.Workbooks.Open("pathtoexcelsheet\sample.xlsx")
    ‘~~> Work with Sheet1. Change as applicable
    Set oXLws = oXLwb.Sheets(1)
rowscount = oXLws.UsedRange.Rows.Count
If rowscount = 1 Then
rowscount = rowscount – 1
Newline = rowscount + 1
tableline = Newline + 1

Else
rowscount = rowscount + 1
Newline = rowscount + 1
tableline = Newline + 1

End If

oXLws.Cells(Newline , 1).Value = hostname
    ‘~~> Loop through each row of the table
    For i = 1 To RowCount
        ‘~~> Loop through each cell of the row
        For j = 1 To ColCount
            ‘~~> This gives you the cell contents
            Debug.Print wrdTbl.Cell(i, j).Range.Text

            ‘~~> Put your code here to export the values of the Word Table
            ‘~~> cell to Excel Cell. Use the .Range.Text to get the value
            ‘~~> of that table cell as shown above and then simply put that
            ‘~~> in the Excel Cell
            With oXLws
                ‘~~> EXAMPLE
                .Cells(tableline , j).Value = wrdTbl.Cell(i, j).Range.Text
            End With
        Next
        tableline = tableline + 1
    Next

    ‘~~> Close and save Excel File
    oXLwb.Close savechanges:=True

    ‘~~> Cleanup (VERY IMPROTANT)
    Set oXLws = Nothing
    Set oXLwb = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing

    Application.Quit
   
End Sub