Copy one column from many files to a single column in a new file 2

[calvinb]

I have a folder with about 100 files in it. Each file consists of a single Word 2003 table with 9 columns. 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 corresponding 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 I've 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!

 

[SkipVought]

hi,

In general I would advise you to use a better tool: Excel. Excel has much MUCH better facility for handling tables. Your request could be handled simply with a loop and MS Query or ActiveX Data Objects. Word is not geared for this kind of processing.

Is there a hard requirement to put the result in a Word table rather than an Excel table?

That said, what code do you have so far?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

I would actually prefer to use Excel but I know absolutely nothing about using VBA in Excel. (I know very little about using it in Word apparently!! lol). If you can help me make it work in Excel that would be fine.
Here it the code I have so far:

"SIN" is the name of the column I'm trying to copy. (Stands for Social Insurance Number)

Sub SelectCopySIN()
'This macro selects the SIN column and moves it to the Test document for one directory only.
'
' **************  Put code to open file here
'
'Counts number of rows in document and subtracts 1 for empty top row
'
NumberOfRows = Selection.Information(wdMaximumNumberOfRows) - 1
   'MsgBox NumberOfRows
'
'---------------------------------------------------------
'
'Sends cursor to first cell in table
'

   Dim rng As Range
   If Selection.Information(wdWithInTable) Then
     Set rng = Selection.Tables(1).Cell(1, 1).Range
     rng.Collapse Direction:=wdCollapseStart
     rng.Select
   End If
'-----------------------------------------------------------------------
'
'Selects column and copies SIN
'
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.MoveDown Unit:=wdLine, Count:=NumberOfRows, Extend:=wdExtend
    Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
    Selection.Copy
'
'Opens "test.docx" so selection can be pasted to end of column
Documents.Open "C:\test.docx"
Documents("test.docx").Activate

'moves cursor to end of column ready to paste selection
'
Selection.EndKey Unit:=wdColumn, Extend:=wdMove
'
'pastes selection at end of column
Selection.PasteAndFormat wdPasteDefault
End Sub
'----------------------------------------------------------

'      ***************** Put code to close file and open next file and rerun macro

 

[SkipVought]

What will the result table be used for?

Will it be processed via code at a subsequent time?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

I will just be using it to search for a SIN. No other code processing will be needed as far as I know.

I need to get all the SIN's from the word documents to one long list so I can use Search to find a particular SIN number. Then I need the Word filename to match the SIN so we can request that person's file from our archive dept. as that is the criteria they need to find our archived files.

I've been looking at Excel since you recommended it and I think it would be the easiest way to do this.

 

[PHV]

I will just be using it to search for a SIN
So, why not simply use the native window's search tool ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

I will... once I get it all compiled from hundreds of files to one large file... That's the plan!!

Thanks

 

[SkipVought]

Don't think you understand PHVs question. You really don't need to load all those files, and what happens as more files are added?

Why not just SEARCH the FOLDER for whatever string?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

You're right! I didn't understand. I didn't know you could search individual documents in a folder from Windows!! I always thought each document had to be opened and then searched individually!!
That's good to know but...

I still need to get those SIN numbers and the document filename into 2 columns in Excel. This Excel file will be given to my co-workers so they can search for SIN numbers themselves. They don't have access to the original Word documents that I want to get the SIN numbers from.

 

[SkipVought]

In Excel, name a sheet SIN Table

In the VB Editor, set a reference to the MS Word Object Library

Paste this procedure in a MODULE

Run from the Macros Menu

Sub LoadTable()
    Dim oFSO As Object, oFile As Object, wd As Word.Application, iRow As Integer, lRow As Long
    Dim wsSIN As Worksheet

'set a reference to MS Word Object Library in Tools > Reference

    Set wd = CreateObject("word.Application")

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set wsSIN = Sheets("SIN Table")
    
    lRow = wsSIN.[A1].CurrentRegion.Rows.Count + 1
    
'put the path to your folder in the [highlight]GetFolder argument[/highlight]

    For Each oFile In oFSO.GetFolder("[highlight]\\YourServer\Path\WordTest[/highlight]").Files
        With wd.Documents.Open(oFile.Path)
            For iRow = 1 To .Tables(1).Rows.Count
                wsSIN.Cells(lRow, "A").Value = .Tables(1).Cell(iRow, 3)
                wsSIN.Cells(lRow, "B").Value = oFile.Name
                lRow = lRow + 1
            Next
            .Close
        End With
    Next
    
    Set wsSIN = Nothing
    Set oFSO = Nothing
    Set wd = Nothing
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

This is cross-posted to VBA Express. I have fully answered with code (for Word) there.

Must say though that I agree this should indeed be in Excel, not Word.

 

[calvinb]

Skip, you are a genius!!! This works perfectly and the code is so short!! I have tried for two weeks and could only get it to copy one file.... and that code was longer than yours!! Thank you so much!!

There is one thing in the output that shouldn't be there. There is a large black dot after each SIN and at the beginning of each file. In the original Word files the first row in each file is blank (I don't know why!!). I think it is picking up a formatting code of some kind. I've attached an image of it below. If that can be removed; great... if not; I can write a macro to clean it up later.

Again, thank you so much!! This is saving me a lot of copy and pasting.

 

[SkipVought]

as fumei stated, you have cross posted in another forum without letting us know. That is not proper forum etiquette. fumei posted a solution for Word, alone in your other thread.

[b]
  Dim sVal
[/b]


            For iRow = 1 To .Tables(1).Rows.Count[b]
                sVal = .Tables(1).Cell(iRow, 3)[/b]
                wsSIN.Cells(lRow, "A").Value = [b]left(sval, len(sval)-2)[/b]
                wsSIN.Cells(lRow, "B").Value = oFile.Name
                lRow = lRow + 1
            Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

Yes, I seen Fumei's comments and I posted a sincere apology on the other forum. I wasn't aware of crossposting so I googled it and now understand what it is and will not be doing it again. Sorry!!

Now, for this additional piece of code you wrote - I've put it in the first procedure you posted right after the 'dim' lines but when I ran the code it causes an error "Invalid or unqualified reference" and it hightlights the ".tables(1)" in this line

For iRow = 1 To .Tables(1).Rows.Count

Am I doing something wrong?

Thanks

 

[fumei]

As stated in the other forum, multiple posting is not something you can not do - just tell us that you have, and where. Thank you for understanding why it is important.

 

[SkipVought]

The Dim statement goes with the other declarations.

The loop is where it was in the original post, with the new code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

Thanks Skip. I don't understand what that does but it works perfectly. No dot anymore! I can now complete this project and make our work routine a little easier thanks to you!


PS. Is there a proper way to complete a thread and give you credit for solving it?

 

[Gavona]

At the bottom left of Skip's post do you see:
[purple]*[/purple]Thank SkipVought
and star this post!
Try clicking on it!

Gavin