How to copy a column from many files and paste into one long column?

[calbais]

I have a folder with about 100 files in it. Each file has 9 colums in a Word 2003 table. I need to copy column 3 of each file in the folder and paste it into another file called "Test" that has 2 columns. I want to put column 3 in the first column of "Test" and I want to put each file name in column 2 of "Test" so I will know where the information in Column 1 came from.

The columns to be copied are all different lengths so I need a way to select to the end of each column, copy it and paste it into the first "Test" column with the corresponding Filename pasted in Column 2 of "Test". Then open the next file and do the same thing over and over until all the files in the folder have beem copied over to the "Test" file.

I've been working on this for 2 weeks now and all the far I got was to select one column in one file and paste it. I could do that manually!!!... I've looked at so much code, I'm totally confused now.... lol

Thanks for any help!

[fumei]

Is there only one table in each file?

[calbais]

Yes. There is from 80 - 120 rows per table but only one table per file.

[fumei]

1. the file with the following code must NOT be in the folder with the files
2. change the variable path (value= "H:\ATest\") to the folder with the files

Option Explicit

Sub GetAllColumns()
' DIR function variables
Dim file
Dim path As String
' target doc objects and variable
Dim ThisDoc As Document
Dim TargetCellText As Cell
Dim TargetCellFileame As Cell
Dim j As Long
Dim k As Long
' source doc variables
Dim SourceText As String
Dim aCell As Cell

path = "H:\ATest\"
j = 1
file = Dir(path & "*.doc")

Set ThisDoc = ActiveDocument

Do While file <> ""
' set target cell objects
Set TargetCellText = ThisDoc.Tables(1).Columns(1).Cells(j)
Set TargetCellFileame = ThisDoc.Tables(1).Columns(2).Cells(j)

' open source doc and accumulate source text
' add to target cell and add filename text
Documents.Open path & file
For Each aCell In ActiveDocument.Tables(1).Columns(3).Cells
SourceText = SourceText & _
Left(aCell.Range.Text, Len(aCell.Range.Text) - 1)
Next
TargetCellText.Range.Text = SourceText
TargetCellFileame.Range.Text = ActiveDocument.Name

' clear objects and variable and close source doc
Set TargetCellText = Nothing
Set TargetCellFileame = Nothing
SourceText = ""
ActiveDocument.Close

' add a new row to target table and increment counter
k = ThisDoc.Tables(1).Range.Cells.Count
ThisDoc.Tables(1).Range.Cells(k).Select
With Selection
.Collapse 1
.MoveRight Unit:=wdCell
' .MoveRight Unit:=wdCell
End With
j = j + 1
' go to next file
file = Dir()
Loop
End Sub

[calbais]

Hi, thanks for your reply. I tried your code but nothing happens. I also asked my question in another forum and it was suggested I use Excel and he provided an example which did the trick.

I seen your post on that forum about crossposting. I googled it and found that it is considered to be in bad taste to crosspost. After considering the effects of crossposting, I can understand why it is frowned upon. It causes people who are kind enough to try to help me to do a lot of work while someone on the other forums are doing the same thing... which is what I did to you. I'm sorry and I won't crosspost again! I was just frustrated after having tried to do this myself for 2 weeks and had to admit I'm not smart enough!!
I do appreciate your efforts and will not do it again.

Now, having said that; how long must I wait for a solution to a post before it is OK to post somewhere else? These forums are a wonderful idea and I want to use them properly!!

Thanks

[fumei]

You do not have to wait to post in other forums. What we ask is that you TELL us.

"I tried your code but nothing happens."

That is simply not possible if you did what I told you.

1. the file with the following code must NOT be in the folder with the files
2. change the variable path (value= "H:\ATest\") to the folder with the files

I am assuming they are in fact .doc files. If not...what are they?

path = "H:\ATest\"

This should the folder with the files. If you have a file Test.doc with the code in it, and the variable path = the folder path to the files, it is not possible that "nothing happened".

Oh....wait. Oooops. My bad. The target file (Test.doc) must have a single table of one row. Ooooops. Sorry, that is totally my fault. That is rather an important piece of information!

[calbais]

Thanks, Fumei. That made a difference. Now it does something but it gives me this error message:

"Run-time error '5941'"
"The requested member of the collection does not exist"

and it highlights this line of your code:

Set TargetCellFileame = ThisDoc.Tables(1).Columns(2).Cells(j)


I was about to send the above to you when I noticed "Columns(2)" which made me wonder if the "Test.doc" should have a single table with one row and 2 columns (I only had one!). I tried that and your code works fine.

Thanks very much for your help! You are a very smart person!! I envy your ability.

[fumei]

Yes, you wanted a table with two columns (one for content, one for the file names).

I do not know how smart I am really. I have been doing this a long time. I teach a VBA course, so a lot is simply habit.

Sorry for the lack of pertinent information! Doh!

However, as was clear in the Tek-Tip posting, if your pupose is to manipulate/extract DATA, it is far better to have things in Excel. Excel is for data, Word is for text.