Compiling all word documents in a folder.

[shekhu]

Hi

Can anyone help me out in completing the code for the following macro.

What I want is that I want to insert all the contents of the documents in a folder to a seprate single document.

This macro does the part of the job, i.e., it asks for the folder first (path) that is fine, but later it makes a list of the names of the documents within the folder in a seprate word document. What I want here is insted of the list, I want the contents of all the documents be pasted in the seprate documents not the file names.

Sub FolderListing()
Dim ListDoc As Document
Dim FolderName As String, FileName As String
With Dialogs(wdDialogCopyFile)
If .Display = -1 Then
FolderName = .directory
Else
Exit Sub
End If
End With
FolderName = Replace(FolderName, Chr(34), "")
Set ListDoc = Documents.Add
ListDoc.Range.Text = "Contents of " & FolderName & vbCr & vbCr
FileName = Dir(FolderName & "*.*")
Do While FileName <> ""
ListDoc.Range.InsertAfter FileName & vbCr
FileName = Dir()
Loop
Set ListDoc = Nothing
End Sub

Thanks in advance.

[shekhu]

Right now I am doing it mannually by going to Insert File (then path) selecting document and click Insert

[Dave]

Not sure if this will help but here's an array method of combining the contents of seperate docs into 1 doc. If there are tables or pics in the docs, I don't think this will be of much help. Also, I'm pretty sure that it can be modified to skip the array part (ie. For each doc in folder) but I don't have a code example handy. Good luck and Happy Holidays! Dave

Sub Collectfiles()
'combines Word docs("fix1","fix2","fix3") into one "summary" doc
'no need for Word reference
'**"C\TestFolder" with fix1.doc, fix2.doc,...
'...fix3.doc and summary.doc must exist
Dim wdapp As Object, Position As String
Dim Bigstring As String, Cnt As Integer, DocArray() As Variant
Dim TempString As String, Myrange As Variant
On Error GoTo Evlmsg1
Set wdapp = CreateObject("Word.Application")
wdapp.ChangeFileOpenDirectory "C:\TestFolder"
DocArray = Array("fix1", "fix2", "fix3")
'combines contents of each doc (TempString) into...
'...large string "bigstring"
For Cnt = LBound(DocArray) To UBound(DocArray)
Position = DocArray(Cnt) & ".doc"
wdapp.Documents.Open FileName:=Position
wdapp.activedocument.Select
Set Myrange = wdapp.activedocument.paragraphs(1).Range
Myrange.SetRange Start:=Myrange.Start, _
End:=wdapp.activedocument.paragraphs _
(wdapp.Selection.paragraphs.Count).Range.End
Myrange.Select
TempString = wdapp.Selection.Text
wdapp.activedocument.Close savechanges:=False
If Len(TempString) <> 1 Then 'empty doc has 1 pilcrow
Bigstring = Bigstring + TempString
End If
Next Cnt

'put combined contents (Bigstring) into summary doc
wdapp.Documents.Open FileName:="summary.doc"
With wdapp.activedocument
.Range(0, .Characters.Count).Delete 'clears summary doc
.content.InsertAfter Bigstring
End With
wdapp.activedocument.Close savechanges:=True
wdapp.Quit
Set wdapp = Nothing
Exit Sub

Evlmsg1:
On Error GoTo 0
MsgBox "You have a File error"
wdapp.Quit
Set wdapp = Nothing
End Sub


edit: whoops forgot what forum I was in. This is actually XL VBA.

[shekhu]

Dave, thanx a lot for your effort, but I dont think this is working, I tried it but failed, please explain further if you can, or someone else could help me solve this issue.

I dont want to specify the folder before hand, i.e., you did "C:\TestFolder"

Every time I do compilation, the folder is different and files are different.

Happy Holidays to you as well.

[fumei]

ListDoc.Range.InsertAfter FileName & vbCr

It lists the file names because...that is what FileName is.

FileName = Dir(FolderName & "*.*")

lists (actions) the filenames. Dir does NOT include the path - FolderName).

So replace:

ListDoc.Range.InsertAfter FileName & vbCr

with:

ListDoc.Range.InsertAfter FolderName & _
"\" & FileName & vbCr

Again, the output (result) of the Dir function does NOT include the path (FolderName). You must supply the path, either to open a file:
FileName = Dir(FolderName & "*.*")
Do While FileName <> ""
Documents.Open Filename:= FolderName & "\" * FileName
' whatever
FileName = Dir()
Loop

or to do an Insert.
FileName = Dir(FolderName & "*.*")
Do While FileName <> ""
ListDoc.Range.InsertAfter FolderName & "\" & FileName & vbCr
FileName = Dir()
Loop

You may want to make it easier by appending the "\" to FolderName.

Or use Application.PathSeparator, as in:

ListDoc.Range.InsertAfter FolderName & Application.PathSeparator _
& FileName & vbCr

Dave, I find it interesting that your comments in your code includes:

" 'no need for Word reference"

Yet you, in fact, declare, set, and use a Word reference. What do you think

Set wdapp = CreateObject("Word.Application")

means?

BTW: for what it is worth, I am opposed to ever using ChangeFileOpenDirectory. I have found that there is very little, if anything, gained by using it. Further, I strongly suggest that if one does use it, that you make a string record of what it was, and when you are finished....put it back to what it was. Returning system values to original states is considered a "best practice". However, again, I have seen very few situations that ChangeFileOpenDirectory is either needed, or desired.

Certainly, in this circumstance, it is not needed at all.

[Dave]

Happy Holidays Gerry! Thanks for your suggestions. A few miscommunications perhaps. First my post, for which I apologize. I did add an edit to my code outlining my mistake and perhaps I should have just deleted the post on edit... but I'm not a big fan of that. The code was just cobbled together out of good will as a starting point. I really don't understand how Word VBA is going to do this, so I will hopefully learn something from less than gracefully bowing out of this post. shekhu I'm sure your in good hands. Dave
ps. Gerry to combine the array of doc file names the use of ChangeFileOpenDirectory seemed necessary. Didn't know it was a bad thing. I was referring to the object library reference.

[shekhu]

Thanks a lot Dave and Gerry, I am sorry, I have tried a lot the code with variations and could not get the right code. My VBA knowledge is very limited. Secondly, I want to repeat that I dont want the list of documents' names in the new document, rather I want only the conents of those documents in a new document. My purpose is to Merge all the documents into one, in a selected folder. If both of you dont mind pl. mention the full code. :doh:

[fumei]

shekhu, the full code? No, I don't think so. Everything you need to get it right is there. However...., me bad. I was not paying full attention.

OF COURSE the code is only listing the filenames.
ListDoc.Range.InsertAfter FolderName & FileName & vbCr

FolderName & FileName & vbCr is a string. It is NOT the file, or rather the file contents. It is a string.

InsertAfter inserts.....a string. And so it does.

BTW: also, after testing, I realized that FolderName includes the path separator, so "\" is not needed.

So....

Change InsertAfter, to InsertFile, as in:ListDoc.Range.InsertFile (FolderName & "\" & FileName)
ListDoc.Range.InsertParagraphAfterNote that you will need to put the separating paragraph (after) as a separate instruction. You can not tack it on the end of the InsertFile instruction.

InsertFile inserts...a file.
InsertAfter inserts text.

Again, the reason you are gettingjust the filenames is that the code inserts text. And FolderName & "\" & FileName & vbCr is text, a string.

Dave, Dave, Dave. There is absolutely no need to apologize for anything at all. ALL responses, comments, efforts, are welcomed and encouraged.

"The code was just cobbled together out of good will "

Indeed. I encourage you to keep up the good will. The purpose of this forum (and others like it) is to to expand knowledge and sharing. Efforts to do so are the point. i sincerely hope you do not feel I was being critical, as that certainly was NOT my intention.

"ps. Gerry to combine the array of doc file names the use of ChangeFileOpenDirectory seemed necessary. "

OK....why does it seem necessary?

ChangeFileOpenDirectory does one thing, and one thing only. It....changes the file open directory. It has no bearing on the filenames in that directory, it has no bearing on any array, or anything except...changing the file open directory.

For the purposes of this thread, an array is not needed. You can process all the files in a folder with the Dir function. There was no call for an array of the names. Even if there was, it would be easy to do.Sub ListStuff()
Dim FileNameList()
Dim file
Dim j As Long
Dim msg As String
file = Dir("c:\temp\temp3\" & "*.*")
Do While file <> ""
ReDim Preserve FileNameList(j)
FileNameList(j) = file
j = j + 1
file = Dir()
Loop
For j = 0 To UBound(FileNameList)
msg = msg & vbCrLf & FileNameList(j)
Next
MsgBox msg
End Subwould display a messagebox listing all files in the "c:\temp\temp3" folder. The list would be contained in the array FileNameList.

However, it is important to understand that the array of filenames is quite an independent thing. An array of the names is not the same thing as the file themselves.

In any case, again, even if you DO want an array of filenames, there still is no need to use ChangeOpenFileDirectory. It only does ONE thing. Changes the file open directory. As you have to pass an explicit parameter to it:

Application.ChangeFileOpenDirectory (Path as string)

you can just as easily use the same string (the path) to access that folder anyway. Either using the Dir function, or a FileSystemObject.

I would be interested in seeing a situation where ChangeFileOpenDirectory is actually needed.