How to transfer a Word 2003 Table to Excel?

[307WRC]

Change the code line with the error to:

Application.StatusBar = "Convert table " & NbTable & "/" & ActiveDocument.Tables.Count

I have changed this code line. The new error I get is "Compile error: Invalid use of property" for this line:

ActiveDocument.Tables.Count (found under " 'For each table in current document ")

Thanks for the macro security links. I read the MS article and it helped. I'll read the rest later.

[jdc2000]

I don't have Office on the computer I am currently using, so I can't test this, but you could try replacing it with this:

CStr(ActiveDocument.Tables.Count)

This line is only used to display a status message in any case, and is not actually needed to convert the table. It just lets you know how far the macro has progressed so you know it is still doing something when it is run.

[307WRC]

I just changed that code line to what you suggested. I opened the VB code window and pasted in the code with the 2 changes. I then went to Tools > References and made sure that Microsoft Excel 11.0 Object Library was ticked and clicked OK in the References window. I saved the doc, closed the VBE and the (supposedly) changed document (Word 2003 itself was still open) and then re-opened it and - nothing had changed. It is still recording the macro. Wasn't the code supposed to make the doc. open as an Excel spreadsheet?

In the Visual Basic editor, after the code has been entered, should I run the macro under the name "Macro1" or "ThisDocument.WordToExcel"?

[General Winters]

After you close the vbe window, run the macro try either name assuming they are exactly the same macro, see what happens then.

[307WRC]

In the Visual Basic editor, after the code has been entered, should I run the macro under the name "Macro1" or "ThisDocument.WordToExcel"?

When I changed the macro to "ThisDocument.WordToExcel" VB says it "Can't execute code in break mode" - this is after I have entered the new code and saved it. The code line highlighted with this message is:

CStr(ActiveDocument.Tables.Count)

This is the complete code, with the 2 changes that jdc200 has suggested:

Option Explicit

Sub WordToExcel()

Dim objExcel As Excel.Application
Dim Wkb As Excel.Workbook

Dim WdCol, WdRow As Integer
Dim XlRow As Integer
Dim idTable As Table
Dim idCell As Cell
Dim idShape As Shape
Dim NbTable As Integer

Dim Ret As Variant

'Open Doc document
With Dialogs(wdDialogFileOpen)
.Name = "*.doc"
Ret = .Show
End With
If Ret <> -1 Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayStatusBar = True

For Each idShape In ActiveDocument.Shapes
If idShape.Type = msoTextBox Then idShape.ConvertToFrame
Next

'Init.
XlRow = 0
NbTable = 0

'Create new Workbook in Excel
Set objExcel = CreateObject("Excel.Application")
Set Wkb = objExcel.Workbooks.Add(xlWBATWorksheet)
Wkb.ActiveSheet.Cells.NumberFormat = "@"

'For each table in current document
For Each idTable In ActiveDocument.Tables
WdRow = 1
WdCol = 1
NbTable = NbTable + 1
Application.StatusBar = "Convert table " & NbTable & "/" & ActiveDocument.Tables.Count
CStr(ActiveDocument.Tables.Count)
'Copy each cell in XL Workbook
Do
Wkb.ActiveSheet.Cells(XlRow + WdRow, WdCol).Formula =
CharCleaner(idTable.Cell(WdRow, WdCol).Range.Text)
idTable.Cell(WdRow, WdCol).Shading.BackgroundPatternColor = wdColorGray15
If Not (idTable.Cell(WdRow, WdCol).Next Is Nothing) Then
Set idCell = idTable.Cell(WdRow, WdCol).Next
WdRow = idCell.RowIndex
WdCol = idCell.ColumnIndex
Else
Exit Do
End If
Loop
XlRow = XlRow + WdRow + 1
Next

objExcel.ActiveSheet.Cells.AutoFit
objExcel.Visible = True

Application.ScreenUpdating = True
Application.StatusBar = True

End Sub



Private Function CharCleaner(Text As String)

Dim i As Integer

For i = 1 To Len(Text) - 2
If Asc(Mid(Text, i, 1)) >= 32 Or Mid(Text, i, 1) = vbCr Then
If Mid(Text, i, 1) = vbCr Then
CharCleaner = CharCleaner & vbLf
Else
CharCleaner = CharCleaner & Mid(Text, i, 1)
End If
End If
Next

End Function

[307WRC]

If the code ever gets straightened out (and thank you jdc 2000 could someone please walk me thru the process from the very beginning, starting with:

1. Open Word
2. Select the doc. that you want to transfer to Excel
3. Make a copy
etc. etc. etc.

While I am not an idiot nor a complete computer novice I feel very adrift in unchartered waters and I'm just basically guessing at what to do. I have never used Visual Basic before and computer code is pretty much Latin to me.

General,

I think that we were typing at the same time. I was doing exactly what you suggested. This is why I want a clear sequence of events. When I open the copied Word doc. should it not be recording macros? If VB ever accepts the code, do I then save that info. or do I select whichever macro name to use?

Thanks for the help.

[jdc2000]

You could zip copies of the Excel and Word files and attach them to a post so I can debug the macro code for you.

[307WRC]

I appreciate your offer. I can zip a copy of the Word file but why would you need an Excel file that doesn't really exist yet (Album List - G thru L)? In my first Word file (Album List - A thru F) I went thru it and manually converted each table to text and then copied & pasted it to an Excel spreadsheet so that is all that I have right now.

[jdc2000]

I would use your manually converted Excel file to compare with the results of the macro to make sure that the automatic conversion is doing as much as possible to make the automatically Excel conversion look as you want it to.

In case you are worried, I have no use for the contents of either or your files. I am only doing this to increase my VBA capabilities and knowledge.

[307WRC]

OK, I understand. I wasn't worried about you seeing the contents of my files - there's no trade secrets or intellectual property to speak of (not counting my time in grading each vinyl album). And it would be good if others could use the conversion capability as well (for their own docs).

Actually, I'm having a bit of a problem with Excel - I want what would be a "normal" paragraph to look like that when viewed in Excel. I don't want the viewer (or me) to have to drag the text out to "AM" just to read a few sentences. I can probably figure that out in time, though.

I will work on zipping my files and posting them. Thanks.

[307WRC]

I don't have a Zip program - which do you recommend (free, no trial)?

[General Winters]

http://www.7-zip.org/

[307WRC]

Thanks. I downloaded 7-Zip earlier today but I'm not too comfortable with it (although I have never been too comfy around any zip programs - don't know why). At any rate, I have installed WinZip 9.0 SR-1 (which I have previously used) and here are the files - - no, they're not. The Word doc. that I have zipped is 2.93 MB and the Excel file is 2.90 MB.

[General Winters]

Then upload the files to a free hosting site such as www.mediafire.com and give the url link to jdc2000.

[307WRC]

Thanks for the MediaFire tip, General (what a great idea!). So here's the link, and it should contain 2 files - one Word in with loads of tables and the other Excel, after I converted all of the tables to text and copied + pasted.

http://www.mediafire.com/?sharekey=3...4e75f6e8ebb871