#1
|
|||
|
|||
Copy tables and transpose them from Word to Excel
Hello everyone!
I need to copy some tables from MS Word to Excel automatically with VBA. but I need the tables to be transposed in Excel. I've attached samples to this post so that you can understand what I mean. I've tried some codes but they are not working like I want. I need this urgently please. If someone is out there reading this and can help out, please do. Save a soul here please! Thank you in advance. |
#2
|
||||
|
||||
The following Word macro should work. Open the document with the tables and run the macro. You'll need to change the path (C:\Path) to the workbook:
Code:
Sub CopyTablesToExcel() Dim xlApp As Object Dim xlBook As Object Dim oTable As Table Dim NextRow As Long Dim oCell As Range Const strWorkBookName As String = "C:\Path\Story - Excel output.xlsx" On Error Resume Next Set xlApp = GetObject(, "Excel.Application") If Err Then Set xlApp = CreateObject("Excel.Application") End If On Error GoTo 0 Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkBookName) xlApp.Visible = True For Each oTable In ActiveDocument.Tables NextRow = xlBook.Sheets(1).Range("A" & xlBook.Sheets(1).Rows.Count).End(-4162).Row + 1 Set oCell = oTable.Rows(1).Cells(2).Range oCell.End = oCell.End - 1 xlBook.Sheets(1).Range("A" & NextRow) = oCell.Text Set oCell = oTable.Rows(2).Cells(2).Range oCell.End = oCell.End - 1 xlBook.Sheets(1).Range("B" & NextRow) = oCell.Text Set oCell = oTable.Rows(3).Cells(2).Range oCell.End = oCell.End - 1 xlBook.Sheets(1).Range("C" & NextRow) = oCell.Text xlBook.Save Next oTable Set xlApp = Nothing Set xlBook = Nothing Set oCell = Nothing Set oTable = Nothing lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Quote:
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
need VBA to Transpose the Data from excel to word based on given criteria(status) | winmaxservices2 | Excel Programming | 1 | 12-19-2014 10:21 PM |
Word macro: copy from different tables into one table | adisl | Word VBA | 4 | 03-25-2014 02:40 AM |
Cannot Copy and Paste Word 2010 Tables | jctech1943 | Word | 8 | 07-03-2012 04:16 AM |
how to copy all ms word tables into excel | rehan129 | Word | 0 | 01-28-2012 10:17 AM |
Excel Tables to MS Word | ripcurlksm | Word Tables | 2 | 09-09-2011 04:59 AM |