Please help with code to open pdf's and modify using access database

[swichman]

I am looking for some visual basic to take a list of file names
(pdf's) in an excell spreadsheet or access query output and use that
list to cycle through a folder on a network drive containing the
referenced pdf files. If the filename in the output equals the
filename on the server, then it would open the file from the server,
paste a text box (in pdf format) in the upper right hand corner, save
the file as the same file name in the same folder on the server and
close the file then move on to the next and do the same. I have
adobe
acrobat professional version 8. This would have to be done for
multiple file names in a list so it would have to be some sort of do
loop or whatnot. I just do not know where to start. Does anybody
have anything like this??
I really appreciate it and would truly be grateful for any helpful
feedback.

 

[Pieter Wijnen]

I don't follow you totally
"paste a text box (in pdf format) in the upper right hand corner, save
the file as the same file name in the same folder on the server"
Do you *really* want to embedd the document in your db or just save the path
to the file?
also the save to the same name must be redundant as the file must be there
in the first place?

I will therefore base my solution on you wanting to store the paths to the
existing files in a table.

Public Sub PDFAddVerified(ByVal Path As String)
' No Error Checking Added!!!
' Minimal Validation

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Qdef As DAO.QueryDef
Dim thQ As String , FName As String


IF VBA.Right(Path) <> "\" Then
Path = Path & "\"
End If

Set Db = Access.CurrentDb
thQ = "PARAMETERS pFileName Text;" & VBA.vbCrlf & _
"INSERT INTO MyTable(FileName) VALUES (pFileName)"

Set Qdef = Db.CreateQueryDef(VBA.vbNullString)
Qdef.SQL = thQ

thQ = "SELECT A.FileName From MyQuery A" & VBA.vbCrlf & _
"WHERE Not Exists (Select 'X' FROM MyTable B" & _
"WHERE B.FileName=A.FileName)"


Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)
While Not Rs.EOF
FName = VBA.Dir(Path & Rs.Fields(0).Value)
If VBA.Len(FName) Then
QDef.Parameters(0).Value = Path & Rs.Fields(0).Value
QDef.Execute DAO.dbSeeChanges
End If
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Qdef.Close : Set Qdef = Nothing
Set Db = Nothing
End If

HTH

Pieter

 

[Bob Quintal]

(e-mail address removed) wrote in 22g2000hsm.googlegroups.com:

I am looking for some visual basic to take a list of file names
(pdf's) in an excell spreadsheet or access query output and use that
list to cycle through a folder on a network drive containing the
referenced pdf files. If the filename in the output equals the
filename on the server, then it would open the file from the server,
paste a text box (in pdf format) in the upper right hand corner, save
the file as the same file name in the same folder on the server and
close the file then move on to the next and do the same. I have
adobe
acrobat professional version 8. This would have to be done for
multiple file names in a list so it would have to be some sort of do
loop or whatnot. I just do not know where to start. Does anybody
have anything like this??
I really appreciate it and would truly be grateful for any helpful
feedback.

Open a query in a recordset, get the fully qualified pathname, run
the dir(FilePath&Name) to see if the file exists, Execute Acrobat
with a java macro to modify your file and save it.

the shellExecute code is here
http://www.mvps.org/access/api/api0018.htm

the code to browse for a folder is here
http://www.mvps.org/access/api/api0002.htm

here is the basic code structure
(not tested, may contain nuts and bugs).

Dim rs as recordset

set rs = currentdb.openrecordset("queryName")
do until rs.EOF
if len(dir(rs!filename)>0 then
'file exists, open acrobat.
fHandleFile(rs!filename, Win_Min)
'do whatever is required in Acrobat
end if
rs.movenext
loop