Copy 4 PDF files to single WORD doc in VBA?

[WinDancer]

Can someone get me started in opening a Word document and copying 4 separate PDF files into that doc and then saving it?
Thanks,
Dave

 

[Mike375]

If you are talking about automating the counterpart of right clicking on the file then Copy and the Paste to Word I think with VBA the copying of the file will be the problem.

If you do the Copy then I think all you would need is to open Word from Access and after Copy have the code pasted into a bookmark.

If you are talking about opening the PDF and copying the text I think that will alos be a high wall to climb and especially since some PDFs won't allow the copying of the text. The few samples I have seen all required that you had the full Adobe program and not just the reader.

If there is a way to Copy in VBA you might be better to post this in the VBA forum because the Word forum is very quiet.

 

[Mike375]

I have found a work around that duplicates right clicking on the file name and then Copy and Paste into Word. I have fiddled on and off with this which is why your post caught my attention. I had the solution in front of me all the time but could not see the forest for the trees

First part embeds the PDF into an OLE Access field. The refererence to Source doc just allows it to get the file name from a textbox entry

With Forms!FormOleLink![OLEBound7]
.Enabled = True
.Locked = False

.OLETypeAllowed = acOLEEmbedded



.SourceDoc = ("C:\Letters\" & Format([Forms]![FormOleLink]![LNameNoDoc]) + ".pdf")


.Action = acOLECreateEmbed

.SizeMode = acOLESizeZoom
End With

Part 2. Macro 1 is has the first action line as a GoToControl and that is the OLE field and the second action line is RunCommand/Copy. The rest opens the Word doc, goes to the Bookmark and pastes into the Bookmark the PDF. You need the Microsoft Word Library reference ticked

DoCmd.RunMacro "Macro1", , ""

Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim WordRange As Word.Range
Set WordObj = CreateObject("Word.Application")
Set WordDoc = WordObj.Documents.Open _
("C:\Letters\0Letter3Mike.doc")
WordObj.Visible = True



Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="a7")

WordRange.Paste

Obviously if you wanted to do 4 of them then it is a repeat process

 

[Mike375]

I just tried it on a "repeat basis" of 6 times.

All worked OK. I added a macro at the end that went back to record 1 and then through the 6 records to delete the OLE objects.

All the inserts into the Word Bookmarks were OK. Makes for a big .doc file size

 

[WinDancer]

Hi Mike
Boy, you have done a ton of work on this- thank you!
When you ran the 6 itterations did you end up with a single Word document that had all the info?
Thanks,
Dave

 

[Mike375]

Single document with the PDFs in the different bookmarks. I left the Word doc open and just repeated the OLE part. Actually I just copied and pasted the block a few times and with a GoToNextRecord at the end of each block. I will put the OLE stuff in a module so it can be called.

A few days ago I also stumbled across a function on AllenBrownes site that lists the files in a folder (you do Immediate Window) and the list goes down the screen. They can then be dragged across and copied and pasted into a table field which means this sort of stuff can have all the file names stored in Access. He also had another version that would get it into a table but it is easier just to copy and paste.

I will post back and attach a DB with a Module with his code.

I use this sort of thing

SourceDoc = ("C:\Letters\" & Format([Forms]![FormOleLink]![LNameNoDoc]) + ".pdf")

So jut the bare file name is entered and it adds the extension. You could could also do the Folder the same way. I just adpated that from what I do with Word.

Just for laughs I also did .doc and an Access db. The DB look like a big icon and double clicking it opens it. The Word doc goes in similar to a PDF in the sense if it is 20 pages (like the one I did) only one page shows and then double click and it opens.

As a side note this code opens an OLE object in Access for both embedded and linked.

Forms![FormOleLink]!OLEBound7.Verb = -2
Forms![FormOleLink]!OLEBound7.Action = 7

This code will link instead of embed

With Forms!FormOleLink![OLEBound7]
.Enabled = True
.Locked = False

.OLETypeAllowed = acOLELinked


.SourceDoc = ("C:\Letters\" & Format([Forms]![FormOleLink]![LNameNoDoc]) + ".pdf")


.Action = acOLECreateLink

.SizeMode = acOLESizeZoom
End With
DoCmd.Close acForm, "PleaseWait"
Exit Sub

 

[Mike375]

Dave,

Attached DB has Module with Allen Brown code. You can see in the table where I paste a bunch in and the query as you can see is to split the names.

The following is the instructions from his website. I just used the first one. If you are not sure what to do with Immediate Window just let me know, it is simple, if you know then you know

To list the files in C:\Data, open the Immediate Window (Ctrl+G), and enter:
Call ListFiles("C:\Data")
To limit the results to zip files:
Call ListFiles("C:\Data", "*.zip")
To include files in subdirectories as well:
Call ListFiles("C:\Data", , True)
In a list box

To show the files in a list box:

1. Create a new form.
2. Add a list box, and set these properties:
   Name lstFileList
   Row Source Type Value List
3. Set the On Load property of the form to:
   [Event Procedure]
4. Click the Build button (...) beside this. Access opens the code window. Set up the event procedure like this:
   Private Sub Form_Load()
   Call ListFiles("C:\Data", , , Me.lstFileList)
   End Sub