Scan selected cells in tables in Word, export to Excel?

J

jamesftoland

I am trying to get a word doc with the same table repeated several
times to extract selected cell data for export to an Excel spreadsheet.
I've stitched together some code based on scenarios I have read on this
mb, but there is so much I don't know...like the looping and the auto
open of Excel!

This is a great site, thanks in advance for any help.
 
D

Default User

And your question is ???
Pls elaborate ...

Some code showing us what you've achieved in the meantime also indicating
where you got stuck ... etc,
would be a bit helpfull

Krgrds,
Perry
 
C

Cindy M -WordMVP-

I am trying to get a word doc with the same table repeated several
times to extract selected cell data for export to an Excel spreadsheet.
I've stitched together some code based on scenarios I have read on this
mb, but there is so much I don't know...like the looping and the auto
open of Excel!

This is a great site, thanks in advance for any help.
Is this message just a "thank you" note, or is there a question in there?

If you're meaning to ask a question, please provide a detailed problem
description.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
J

jamesftoland

Ok, thanks for the replies, my bad on the minimal desc.

What I have is a Word doc with a table repeated multiple times. each
table has 8 cells, same cell header, different text data in each. I
want a macro to scan the word doc for all the tables, and take 6 of the
8 cells out of each table and export them to a new Excel worksheet,
with each row in that worksheet representing the table that was
scanned, and each column the cell data (so, 6 columns). The rows
appearing in excel would depend on the number of tables in the word doc
that was scanned.

In other words,
one word doc = one excel file
one table in that word doc = one row in the excel file

Here is my frankenstein code so far:
__________________________________________

Public Sub ScanDocumentForTable(sDocPath As String, wdDocName As
String)
Dim WordDocument As Object
Dim wdApp As Object
Set wdApp = GetObject("", "Word.Application")
DoEvents
wdApp.Application.Documents.Open sDocPath & wdDocName & ".doc"
wdApp.Application.Visible = False

DoEvents
If wdApp.ActiveDocument.Tables.Count >= 3 Then
Dim tblNumber As Integer
For tblNumber = 3 To wdApp.ActiveDocument.Tables.Count
If Left(wdApp.ActiveDocument.Tables(tblNumber), 9) = "Step Type" Then
wdApp.ActiveDocument.Tables(tblNumber).Range.Copy

ActiveDocument.Tables(1).Cell(3, 1)
ActiveDocument.Tables(1).Cell(3, 1)
ActiveDocument.Tables(1).Cell(3, 2)
ActiveDocument.Tables(1).Cell(7, 1)
ActiveDocument.Tables(1).Cell(1, 2)
ActiveDocument.Tables(1).Cell(3, 3)
ActiveDocument.Tables(1).Cell(3, 4)

Exit For
End If
Next tblNumber
End If
End Sub

------------------------------
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = False
DoEvents
ExcelSheet.ActiveSheet.PasteSpecial Format:="HTML", Link:=False,
DisplayAsIcon:=False, NoHTMLFormatting:=True
ExcelSheet.ActiveSheet.Rows("1:1").Font.Bold = True
ExcelSheet.Application.Cells.Font.Name = "Courier New"
ExcelSheet.Application.Cells.Font.Size = 10
ExcelSheet.Application.Cells.EntireColumn.Autofit

ExcelSheet.SaveAs sDocPath & wdDocName & ".xls"
ExcelSheet.Application.Quit
wdApp.Application.Quit
Set ExcelSheet = Nothing
Set wdApp = Nothing
Exit For
End If
Next tblNumber
End If
End Sub
 
C

Cindy M -WordMVP-

What I have is a Word doc with a table repeated multiple times. each
table has 8 cells, same cell header, different text data in each. I
want a macro to scan the word doc for all the tables, and take 6 of the
8 cells out of each table and export them to a new Excel worksheet,
with each row in that worksheet representing the table that was
scanned, and each column the cell data (so, 6 columns). The rows
appearing in excel would depend on the number of tables in the word doc
that was scanned.
OK, better. But a table with eight cells could be 1 row with eight cells
across, or two rows with four cells each, or...

Could you please be more specific?

To get you started, you can pick up all the tables in a document using

Dim tbl as Word.Table
For each tbl in ActiveDocument.Tables
'Do stuff here
Next

In order to access an Excel file, you want to activate a reference to the
Microsoft Excel library via Tools/References.

Dim xlApp as Excel.Application
Dim xlWB as Excel.Workbook
Set xlApp = New Excel.Application 'or see GetObject in the VBA Help
Set xlWB = xlApp.Workbooks.Add

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top