PDA

View Full Version : [SOLVED] Merge specific word files from excel VBA



Tim AAA
07-16-2019, 07:04 AM
Hi!

I have recently been working on a program that is supposed to create one word file from multiple files which i define in a spreadsheet.
The process should go as followed:
-In Column A I enter the paths of the word files I want to merge
-I activate the macro and using the paths the files should merge

I have the files set up in such a way that every file is always exactly a whole number of pages long (e.g. 1,2,3,4,5...)
I therefore want that the files do not overlap on a page.
So if I have one file of 1 page and another of 2 pages the end result should be a file consisting of 3 pages.
For clarification I have added two files and what the final product should be:
246042460524606

The only thing I found that came remotely close to what I'd like my code to do is:
https://www.extendoffice.com/documents/word/969-word-merge-multiple-documents.html

I'm at a dead end here so any help would be greatly appreciated.

Kenneth Hobs
07-16-2019, 08:18 AM
Welcome to the forum!

If you put the files into a folder, and then ran the macro in the link, did it produce the file needed? If so, the macro is easily modified to suit.

The usual DOS/CMD method to "copy /b" method corrupts the final output. I would imagine that you would have some other issues as well. e.g. A file is protected or contains an SQL.

Tim AAA
07-17-2019, 12:29 AM
Thanks for the welcome!

The two problems with the macro from the link I have is that it doesn't give each file a different page.
E.g. my final file would look like this (Using the same docs as in my question):

Page1

Page2
"On the next page:"
Page 2 (continued)

The second thing I would like this macro to do is to be usable from excel vba.

So these are the two modifications I would like to make to this macro.
Furthermore, I did not experience any errors, corruptions or any other issues.

Kenneth Hobs
07-17-2019, 05:22 PM
I don't know what the continued thing is about. I guess it might be some sort of page numbering? If so, that would be something extra where I would need example files.

As for (2), this will work sometimes. Failures can happen for readonly files, password protected files, not DOCX file types, SQL embedded codes, etc. Note how I gave you a way to pass the merge fullname files in an array and the merged fullname file. I added no error checks like existing paths or filenames. That is the user's responsibility in Main().


Sub Main()
Dim a, s$
a = Array("c:\t\1.docx", "c:\t\2.docx")
s = "c:\t\1_2.docx"
MergeDocxs a, s

Shell "cmd /c " & """" & s & """", vbNormalFocus
End Sub


Sub MergeDocxs(aDocx, mDocx As String)
Dim oo As Object, o As Object, i As Integer
Dim wdFormatXMLDocument As Integer, wdLine As Integer
Dim wdPageBreak As Integer, wA As Object

Application.ScreenUpdating = False

wdFormatXMLDocument = 12
wdLine = 5
wdPageBreak = 7
Set wA = CreateObject("Word.Application")
Set oo = wA.Documents.Open(aDocx(LBound(aDocx)))


For i = LBound(aDocx) + 1 To UBound(aDocx)
Set o = wA.Documents.Open(aDocx(i))
wA.Selection.WholeStory
wA.Selection.Copy
o.Close False
oo.Activate
wA.Selection.EndKey Unit:=wdLine
wA.Selection.InsertBreak Type:=wdPageBreak
wA.Selection.Paste
Next i
oo.SaveAs2 Filename:=mDocx, FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, CompatibilityMode:=15


oo.Close False


Application.ScreenUpdating = True
End Sub

Tim AAA
07-18-2019, 12:31 AM
The continued thing was just to show that file 2 contained two pages.
This code seems to work perfectly!
Thank you so much for your help!