|
-
July 10th, 2009, 01:11 AM
#1
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!
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 10th, 2009, 01:31 AM
#2
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.
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 10th, 2009, 11:49 PM
#3
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
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 01:48 AM
#4
VBA is definitely the method to use. Have you tried the macro? If so, what were the results?
-
July 11th, 2009, 02:09 AM
#5
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 12:43 PM
#6
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.
-
July 11th, 2009, 04:11 PM
#7
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.
Last edited by General Winters; July 11th, 2009 at 04:14 PM.
Current PC
Zoostorm
Windows 7 Home Premium 64bit, (XP Mode virtual pc enabled)
WEI 7.4, 7.6, 7.9, 7.9, 5.9
Intel Core i5-2310, 8GB Ram
Geforce GTX 660 OC
Samsung Syncmaster SA300 23.6" monitor
Folding@home
User Name Sintares
Team guru3d
There is no such thing as overkill. 'Open fire' and 'is it dead?' are the only sane options when threatened.
-
July 11th, 2009, 05:07 PM
#8
GW - Thanks for the reminder. The VBA macro editor window does open with no Code window showing.
-
July 11th, 2009, 05:31 PM
#9
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?
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 06:00 PM
#10
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
-
July 11th, 2009, 07:37 PM
#11
 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.
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 09:21 PM
#12
You have created and saved the macro, did you then actually run it ?
To convert a file:
Tools -> Macro -> Macros -> WordToExcel.
-
July 11th, 2009, 11:13 PM
#13
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.
Last edited by 307WRC; July 11th, 2009 at 11:16 PM.
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 11:27 PM
#14
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!
Last edited by 307WRC; July 11th, 2009 at 11:30 PM.
Reason: typo
“If Ernest Hemingway was going to go big-game hunting in Africa, Hunter S. Thompson wanted to use a submachine gun to hunt wild boar in Big Sur, Calif. He was dangerous, like handling nitroglycerin, and he liked to keep it that way.”
Douglas Brinkley, executor of the Thompson estate
-
July 11th, 2009, 11:43 PM
#15
Thread Information
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|
Reply With Quote
