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

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.

0
swichman
9/22/2007 2:42:12 AM
access 16762 articles. 3 followers. Follow

2 Replies
647 Views

Similar Articles

[PageSpeed] 17

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


<swichman@ch2m.com> wrote in message 
news:1190428932.767642.318750@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.
> 


0
Pieter
9/22/2007 7:30:52 AM
swichman@ch2m.com wrote in news:1190428932.767642.318750@
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


-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
9/22/2007 11:13:24 AM
Reply:

Similar Artilces:

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Reporting from Project Server
I dont know if i need to ask this question here or in the Access section. I have an ODBC connection to the Project Server database so I can make reports through Access. Access' limit of 255 fields per table is causing me some trouble. for example, the MSP_VIEW_PROJ_PROJECTS_ENT table has well over 255 fields. Access only shows me the first 255 fields. how can I change that so I can see all the fields in that table? thanks, Hadi Hadi, I have not tried this yet it may be a viable option. Have your DBA create a view that pulls the key fields to this table and the specifi...

Stop My Documents from opening all the time?
Is there anyway to stop My Documents from opening everytimg I start a Windows Explorer session? I guess there's no future in trying to delete it, because so many products try to put stuff there. Right? Laurel wrote: > Is there anyway to stop My Documents from opening everytimg I start a > Windows Explorer session? > If you want Explorer to open somewhere else, simply modify the properties of the shortcut you're using to open it. All you need do is create/modify the target field of your Windows Explorer shortcut to point where you want it. Co...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

owa cant open ppt attachments
i have an exchange 2003 cluster when a user tries to open or save a powerpoint attachment it hangs at 97% all other attachments open fine any ideas thanks ...

Unable to delete empty database
I recently moved a group of users out of one database into another Database within the same storage group. After successfully moving all users (except SMTP and SystemMailbox) out of the 1st database I attempted to delete the database only to receive an error: One or more users currently use this mailbox store. These users must be moved to a different mailbox store or be mail disabled before deleting this store. Id no: c1034a7f Exchange System Manager Can I delete this mailbox via ADSIEDIT? Is this the recommended alternative? Thanks, BJ Use the AD Users and Computers snap-in ...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Unable to open .docx documents in Word 2002
I cannot open word documents sent with .docx extendion in word 2002. I checked my language setting and it is English. What is wrong? Download the Compatibility Pack. http://www.microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Johnpm" <Johnpm@discussions.microsoft.com> wrote in message news:43E248E8-5D83-4E91-9743-CE12CCD6A443@microsoft.com... >I cannot open word documents sent with .docx extendi...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

cannot open hyper links in outlook
when I try to open a hyperlink in outlook, I get the following message: This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator. ----- I am the system administrator. HELP This is a problem with IE, not Outlook. You need to reset your internet settings in IE's Tools, Internet Options, Advanced tab. (Or Control Panel, Internet options, Advanced tab). See http://www.slipstick.com/problems/link_restrict.htm for more information. "Donald McNeely" <Donald McNeely@discussions.microsoft.com>...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

Math Worksheet Database
I am interested in developing/acquiring an Access 2003 database to generate (K-5 grade) math worksheets where I can track student results. I would hope not to reinvent the wheel. Thank you for any assistance. ...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

Access to User Calendar
I have a user called small conference room that is used to schedule meetings on its calendar. I would like to link the calendar from our intranet site to the calendar with a UNC path. I am calling outlook: and I can get to my local mailbox and public folders but I am unable to connect to another users calendar. I am running Exchange 2003 and Outlook 2003. Is there some security modifications that need to be done? Any help is appreciated. Thanks, Steve I believe that you will need full mailbox rights. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!&...

I need to print a word in PDF from excel.
Hi, In my excel Macro, I update several values and then open a word linked with that excel. Finally what I need to do is to print that word to PDF. I already have a PDF Printer, so I just need the code to do it. Can anybody help me? Thanks in advance. Gast=F3n. Habilita la impresora predeterminada como la PDF y ya lo puedes imprimir a al formato deseado Gracias Francisco,=20 Tienes alg=FAn codigo para ello? gracias Repitiendo: Desde el panel de impresoras asigno primero la que me interesa y despues ejecuto mi macro desde el archivo excel asignando la instruccion sig: midefaultes = a...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

microsoft.public.access.conversion
...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

Excel VBA
When you select the autofilter, the drop down list is created from the unique data in that column. Is there any way to customize this list, so the changes are permanent? For instance, I might have in one(1) column the following 4 row entries: red, blue, green (all are single cell entries) red, blue red blue, green I want my autofilter drop down list to read: red instead of red, blue, green blue red, blue green red blue, green (like normal) If I chose red from the drop down list...

How to set "licence" for Access 2007 database?
Hi I developed an Access 2007 db to a client. Now I want to make a year based licence for that database that the client must pay if they want to continue using the database after year. It must be so that database cannot be used after this date. How I can accomplish this? Thanks! On Mon, 12 Apr 2010 13:14:17 -0700 (PDT), Sandroid <santeri.virtanen@gmail.com> wrote: >Hi > >I developed an Access 2007 db to a client. Now I want to make a year >based licence for that database that the client must pay if they want >to continue using the database after year. It mu...

Items in this message are still loading. Please wait a moment and try again.
Get this error message when trying to print an HTML email using Outlook 2002 10.6515.6735 SP3. I've seen lots of single post threads regarding this issue with no responses. The problem is, the moment lasts up to an hour. Are there any settings that can be tweaked to speed this process up? Could it possibly be a printer issue? This just recently started happening. Any suggestions are appreciated. I haven't ever experienced this but are you on a slow Internet connection? An hour is an awful long time (which I'm sure you know already) "Geoff" <geoff.warner@gmail...