How to transfer a Word 2003 Table to Excel?

I have recently finished cataloging, grading and pricing my vinyl LP record album collection, which numbers close to 1000. I used individual tables in Word 2003 for each album (see the attached file). I want to transfer all of the info. that is contained within each table to an Excel cell to create 4 Excel docs (A thru F, G thru L etc.). The trouble is, I know virtually nothing about Excel. I'm sure that it's just a matter of playing with the cell sizes but, again, I am pretty lost here. I want to keep the general look of my Word docs. Could someone please walk me thru this (baby steps please!)?

Thank you!:)

I did just now find that if, in Word 2003, I click Convert > Table to Text > Separate text with - Paragraph marks the resulting XLS doc. is very similar to my Word table (see attached). I have approx. 120 tables per Word doc. so it would be great if the process could be automated.

I can't upload an .xls file so please see my screen shot.

I found some info. in a 2006 Google Answers thread that I think may work if I can figure out one thing. Someone ("Rainbow") at first said to change the Save as type to Text with Layout (*.ans) but Word 2003 does not support this. A bit further down the GA thread "MrBzzT" says to do this:

Hav a look to the VBA code (Word) below which converts Word tables into Excel file.
It's not perfect...but it's the best solution i find.

Create a new Word document.
Tools -> Macros -> Visual BASIC Editor
Paste the code below
Tools -> Reference -> Select Microsoft Excel 110 object library
Save your new document

To convert a file:
Tools -> Macros -> Macros -> WordToExcel.
The cells of your Word document which could be converted are gray, the
others still white.

I've got a test Word 2003 doc opened with the Visual BASIC Editor but where do I paste the following code?

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
'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

VBA is definitely the method to use. Have you tried the macro? If so, what were the results?

Quote:

---

Originally Posted by jdc2000

Have you tried the macro? If so, what were the results?

---

No! Where do I paste the code?? Sorry, I'm a bit exhausted from manually converting all of my Word tables to text and then copy + pastin' everything into my A thru F Excel spreadsheet. I am including photos of the albums where I have them and the size did not line up perfectly with the cell sizes so I manually pasted about 50+ photos, which meant that I could only paste my text-only album entries up to a point where an album had a photo. Yow! There's gotta be an easier way - - but I at least know how to manually create the spreadsheet, even tho it sux that way.:mad:

What I have so far (this is an example of entries with photos) -

https://discussions.virtualdr.com/im...2009/07/19.jpg

For entries that do not have photos I just have a line that reads Photos available upon request (directly under the "Label" cell). Thanks for asking btw!:)

Using a VBA (Visual Basic for Applications) Macro would enable you to automate the manual task of cutting and pasting the entries. I have written macros to perform similar functions. The steps needed were included in one of your prevoius posts. You will want to work with a COPY of your original Word document, and a NEW Excel spreadsheet while testing.

If you want me to take a look at what you have and try to make the Word Macro work for your conversion, you will need to create and post a .zip file attachment with your Word document(s) amd your manually converted Excel document(s) so I have copies of the before and after versions. I can then try to provide you with a Macro that can perform the conversion automatically.

Steps to insert and run the Macro:

Create a copy of your Word document.
Tools -> Macros -> Visual BASIC Editor
Paste the code below
Tools -> Reference -> Select Microsoft Excel 110 object library
Save your new document

To convert a file:
Tools -> Macros -> Macros -> WordToExcel.
The cells of your Word document which could be converted are gray, the
others still white.

I think what 307WRC is saying that when he opens the VBE he sees there is nowhere to paste the code, in which case i would assume he needs to press f7 (or view-code) to open the coding window where he can paste the example code he has found.

GW - Thanks for the reminder. The VBA macro editor window does open with no Code window showing.

Thanks, I was trying to say that I couldn't find the coding window. Okay, I have made a copy, opened the VBE, opened the coding window and pasted the code from my first post (the Google Answers thread). Once that code has been pasted do I hit Enter or what? Also, the coding window has 2 drop-down selections: on the left I can choose either General or Document and on the right, if General is selected, the choices are CharCleaner and WordToExcel. Which should I select for these boxes?

You don't have to select anything, the dropdown selections just jump the code window to different parts of the code, helpful when the coding is long and you have many declarations.

Remember to Tools -> Reference -> Select Microsoft Excel 110 object library ie make sure there is a tick at excel 11.0 library

Quote:

---

Originally Posted by General Winters

Remember to Tools -> Reference -> Select Microsoft Excel 110 object library ie make sure there is a tick at excel 11.0 library

---

Oh, that was not ticked before. I just did this over again from scratch and I did tick Microsoft Excel 11.0 Object Library, and then OK, but nothing happens. What do I do after clicking OK in the References - Project window? I saved the project (Ctrl+S) and closed the VBE window but when I open the saved doc it looks the same, i.e. a Word 2003 doc with lots of tables, as when I started.

You have created and saved the macro, did you then actually run it ?

To convert a file:
Tools -> Macro -> Macros -> WordToExcel.

When I try to run the Macro I get a MS Visual Basic window telling me that "The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros."

I changed the Security level of Macros from High to Medium but I am not being prompted to enable a macro when I try to convert my doc. with Word (Tools -> Macro -> Macros -> WordToExcel). Which should I use to open the converted doc: Word, Excel or Visual Basic? Does VB need to be open?

Edit: I just changed the Macro Security level to Low and I still get the "macros in this project are disabled" window.

I figured out the Macro problem - I needed to Record the macro so I am no longer prompted to enable it. Btw, I am at High security. When I tried to open my doc. with the macro enabled I got a "Compile error: Syntax error" for this line:

Application.StatusBar = "Convert table " & NbTable & "/" & (found under " 'For each table in current document ")

At least we're making some progress!:cool:

Possibly useful links for the macro security issue:

http://support.microsoft.com/kb/302632

http://www.the-pc-guru.com/Microsoft...acros_in_t.php

http://help.lockergnome.com/office/m...ict683967.html

http://www.google.com/search?hl=en&q...+this+&aqi=g10


Change the code line with the error to:

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

Quote:

---

Originally Posted by jdc2000

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.

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.

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"?

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

Quote:

---

Originally Posted by 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

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.

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.

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.

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.

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.

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

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

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.

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

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

I downloaded the files, and I copied the macro code. It is too late for me to work on it tonight though. I'll look at it later. Thanks.