Retrieve filename from a PDF OLE object

[gregoryhu]

Hi,
I have many pdfs and i want to make a database with title, author and link Fields
My main problem is the data entry

I want to make a form with a big OLE object, and when i drug&drop the pdf file
to the OLE , automatically the full path and filename will fill a textbox(bounded with Title field) ( i want filesize too if it is possible) and after i confirm i will proceed to the next record etc etc.

How can i do that

Please help

[gregoryhu]

Hi,
I have many pdfs and i want to make a database with title, author and link Fields
My main problem is the data entry

I want to make a form with a big OLE object, and when i drug&drop the pdf file
to the OLE , automatically the full path and filename will fill a textbox(bounded with Title field) ( i want filesize too if it is possible) and after i confirm i will proceed to the next record etc etc.

How can i do that

Please help

The reason i want the Ole object is that i want to see the first page of the pdf where the authors are written

[ADezii]

Hi,
I have many pdfs and i want to make a database with title, author and link Fields
My main problem is the data entry

I want to make a form with a big OLE object, and when i drug&drop the pdf file
to the OLE , automatically the full path and filename will fill a textbox(bounded with Title field) ( i want filesize too if it is possible) and after i confirm i will proceed to the next record etc etc.

How can i do that

Please help

I'm not sure but I think the Default ACtion for Dragging-N-Dropping an Object into an OLE Field is to embed the Object. Possibly in the MouseUp() Event, you would have to programattically change the OLE Type to Linked via code similiar to Me![OLE1].Action = acOLECreateLink, then extract the Path from the SourceDoc Property if it even exists at the time of this operation. I can, however, offer another option:

1. Use the File Dialog Object to search for, and retrieve, your .pdf Files.
2. Once you have selected the file, it is a simple matter to obtain its Path.
3. Write the Path to a Field on your Form.
4. Write code to add the Linked .pdf file to the underlying Table.

NOTE: What also complicates matters, is that Access does not support Drag/Drop Events as does Visual Basic. I wouold wait and see what the other Moderators/Experts say on this matter but if you are interested in the alternative route, just let me know.

[theaybaras]

I have a related question to your response. I have a database with a whole lot of articles. Each one has a related PDF file. I would like to have a link made to the pdf based on the information entered into the form.

For instance, I will be naming all PDF files in the following convention:
Journal Title, Volume, Page-Range.pdf
how can I have a link created automatically (maybe in an unbound field) when the User puts in the proper information into the form.
i.e.

Journal of Traumatic Stress, 19, 571-595

create a link that that when clicked opens the file:

Journal of Traumatic Stress, 19, 571-595.PDF in the user's default application.

Thanks!

theAybaras

[ADezii]

I have a related question to your response. I have a database with a whole lot of articles. Each one has a related PDF file. I would like to have a link made to the pdf based on the information entered into the form.

For instance, I will be naming all PDF files in the following convention:
Journal Title, Volume, Page-Range.pdf
how can I have a link created automatically (maybe in an unbound field) when the User puts in the proper information into the form.
i.e.

Journal of Traumatic Stress, 19, 571-595

create a link that that when clicked opens the file:

Journal of Traumatic Stress, 19, 571-595.PDF in the user's default application.

Thanks!

theAybaras

If you are going to be maintaining a consistent naming convention such as:
Journal Title, Volume, Page-Range.pdf, then it is probably a good suggestion to work backwards:

1. Rename all your .pdf files to be consistent with your predefined format. The keyword here is consistent.
2. Create an OLE Object Field to contain the Link to the .pdfs.
3. Add the OLE Object Field to the Form.
4. Set the Required Property of [Journal Title], [Volume], and [Page Range] Fields to No.
5. Go to each Record and select the appropriate .pdf for the Record via the File Dialog.
6. Parse the File Name and write to the [Journal Title], [Volume], and [Page Range] Fields on the Form.
7. Write the code to Save and Link the .pdf File and also to have it displayed within the OLE Field on the Form.
8. Save the Record and move onto the next Record.
9. Avoid the commas as a Delimiter in a File Name, possibly use the underscore instead (_).

NOTE: I will not start anything unless I am absolutely positive that this is what you want. This process will take a little time on my part to implement.

[theaybaras]

If you are going to be maintaining a consistent naming convention such as:
Journal Title, Volume, Page-Range.pdf, then it is probably a good suggestion to work backwards:

1. Rename all your .pdf files to be consistent with your predefined format. The keyword here is consistent.
2. Create an OLE Object Field to contain the Link to the .pdfs.
3. Add the OLE Object Field to the Form.
4. Set the Required Property of [Journal Title], [Volume], and [Page Range] Fields to No.
5. Go to each Record and select the appropriate .pdf for the Record via the File Dialog.
6. Parse the File Name and write to the [Journal Title], [Volume], and [Page Range] Fields on the Form.
7. Write the code to Save and Link the .pdf File and also to have it displayed within the OLE Field on the Form.
8. Save the Record and move onto the next Record.
9. Avoid the commas as a Delimiter in a File Name, possibly use the underscore instead (_).

NOTE: I will not start anything unless I am absolutely positive that this is what you want. This process will take a little time on my part to implement.

Wow, thanks for the reply :)
I'm not looking to actually view the pdf within the database, just have a link to the file that would open the file in acrobat.
All of my current pdfs are already named (without exception) to this convention, which was no chore as I have almost 9000 articles. With that number I most certainly don't want to embed in the database if I don't have to! ;)
I was hoping that there was some way by coding/etc. to automate the linking of the file so I would not have to point 9000 records to 9000 files by hand!

I was hoping that since an identical version of the components of my naming convention is being entered into the fields in my record that I could somehow pull the file from "../PDF/Journal Title_Volume_Page-Range.PDF

I'm pretty much completely virgin to sql and coding in access but any tips would be great! I really appreciate your help!

[theaybaras]

Well, I have figured out a way to do this using a macro.

I created a macro that sets the hyperlink address of a button [a_pdflink] to:

"pdf/" & [a_title] & "_" & [a_volume] & "_" & [a_pages] & ".pdf"

then set the form's on event properties to run the macro. Then whichever record the person is on, the button links to the right file.

I'm sure there are much better ways to do this, but I at least have something that will work!

Thanks so much for your help!

Now I just have to figure your a way for them to search the records by more than one field at a time (i.e. search for the same info as above) and I'll be all set!

Thanks again!

theAybaras

[ADezii]

Wow, thanks for the reply :)
I'm not looking to actually view the pdf within the database, just have a link to the file that would open the file in acrobat.
All of my current pdfs are already named (without exception) to this convention, which was no chore as I have almost 9000 articles. With that number I most certainly don't want to embed in the database if I don't have to! ;)
I was hoping that there was some way by coding/etc. to automate the linking of the file so I would not have to point 9000 records to 9000 files by hand!

I was hoping that since an identical version of the components of my naming convention is being entered into the fields in my record that I could somehow pull the file from "../PDF/Journal Title_Volume_Page-Range.PDF

I'm pretty much completely virgin to sql and coding in access but any tips would be great! I really appreciate your help!

You definately do not want to Embed or Link after reading this, and Hyperlinks may be a little troublesome, but believe it or not there is another option. I use this option often for circumstances similar to yours and involves virtually no overhead. For each Record, store the Absolute Path to the associated .pdf File in a Text Field. On your Form, place a button with the Caption Open in Adobe. In the OnClick() Event the ShellExecute() API Function is executed and will open the .pdf File in Adobe based on its extension only, no Path to Adobe is needed. This Routine also contains extensive Error Checking which will pin point potential problems. It works like a charm. If you are interested, let me know.

[Steve123]

ADezii,

Would you mind posting your solution that uses the absolute file path?

Thanks!

[ADezii]

ADezii,

Would you mind posting your solution that uses the absolute file path?

Thanks!

I 'will' get back to you as soon as I get a chance.

[ADezii]

ADezii,

Would you mind posting your solution that uses the absolute file path?

Thanks!

This is one possible solution to your dilemma. Its major advantage is that there is virtually no overhead involved as compared to the Embed or Link Options and that you don't even need the Path to Adobe Reader. The *.pdf is opened virtually by means of its extension only. The code can also be used to execute any other type of file extension that is Registered on the User's System such as: *.doc, *.txt, *.mdb, *.xls, *.exe, *.bmp, etc. Give it a try and let me know what you think. Follow each step in sequence:

1. Place the following code in the Declarations of a Standard Code Module.
   

   Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, _
   ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
   Public Success As Boolean

2. Copy and Paste the following Function into a Standard Code Module. As previously indicated, it has extensive Error Handling built into the Function itself.
   

   Public Function Execute_Program(ByVal strFilePath As String, _
       ByVal strParms As String, ByVal strDir As String) _
       As Boolean
    
       'run program 
       Dim hwndProgram As Integer
       hwndProgram = ShellExecute(0, "Open", strFilePath, strParms, strDir, 3)     '3 ==> Show Maximized
    
     'evaluate errors (if any)
     Select Case (hwndProgram)
       Case 0
           MsgBox "Insufficent system memory or corrupt program file.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 2
           MsgBox "File not found.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 3
           MsgBox "Invalid path.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 5
           MsgBox "Sharing or Protection Error.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 6
           MsgBox "Seperate data segments are required for each task.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 8
           MsgBox "Insufficient memory to run the program.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
      Case 10
           MsgBox "Incorrect Windows version.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 11
           MsgBox "Invalid program file.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 12
           MsgBox "Program file requires a different operating system.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 13
           MsgBox "Program requires MS-DOS 4.0.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 14
           MsgBox "Unknown program file type.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 15
           MsgBox "Windows program does not support protected memory mode.", 0, "Error running " & strFilePath
            Execute_Program = False
              Exit Function
       Case 16
           MsgBox "Invalid use of data segments when loading a second instance of a program.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 19
           MsgBox "Attempt to run a compressed program file.", 0, "Error running " & strFilePath
            Execute_Program = False
              Exit Function
       Case 20
           MsgBox "Invalid dynamic link library.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case 21
           MsgBox "Program requires Windows 32-bit extensions.", 0, "Error running " & strFilePath
           Execute_Program = False
             Exit Function
       Case Else
       End Select
    
       Execute_Program = True
    
   End Function

3. Create a Field in the Table that is the Record Source for your Form and call it FilePath (Data Type = TEXT).
4. Create a Text Box on your Form and set its Control Source to FilePath. Name it txtFilePath.
5. Add the Absolute Path to your *.pdf Files into this Text Box. e.g. C:\PDF Files\ Finance Report.pdf.
6. Add a Command Button to your Form, preferably next to txtFilePath. Set its Caption to View PDF File, and its name to cmdViewPDF.
7. With the Paths to various *.pdf Files stored in the Table, you are ready to go.
8. In the Form's Current() Event, add the following code.
   

   If IsNull(Me![txtFilePath]) Then
     Me!cmdViewPDF.Enables = False
   Else
     Me!cmdViewPDF.Enables = True
   End If

9. Add the following code to the Click() Event of cmdViewPDF.
   

   Success = Execute_Program(Me![txtFilePath], "", "")

10. If a *.pdf File exists for a given Record, the View PDF File Command Button will be enabled, if no *.pdf File exists (IsNull(Me![txtFilePath]) = True), the Button will be disabled.
11. Clicking on the Button when a *.pdf File exists, assuming the Path is correct, will Open the File within Adobe Reader. The Function itself virtually accounts for every conceivable Error contingency.
12. Hint: By the use of a FileDialog Control, the process of adding *.pdf Files to existing Records will be greatly simplified. Simply select the proper File then write it to txtFilePath after which it can be saved to the underlying Table. If you need to know how to do this, please let me know.
13. I think I have given you enough food for thought. Good Luck and Good Night!

[Steve123]

ADezii,

Thank you for the code...it works perfectly! As a follow up question, I would now like to know if it would be possible to add multiple attachments to a single record. I do not know the total number of files the user will add to the record, so I need a solution that is dynamic.

Would it be possible to create a separate attachments table that contains an attachments field and an ID field, where the attachments field contains the absolute reference to each file, and the ID field contains the record identifier the file is associated with? This would require either a table or some type of listbox on the form where the user would select the individual files from.

Thank you again,

Steve

[ADezii]

ADezii,

Thank you for the code...it works perfectly! As a follow up question, I would now like to know if it would be possible to add multiple attachments to a single record. I do not know the total number of files the user will add to the record, so I need a solution that is dynamic.

Would it be possible to create a separate attachments table that contains an attachments field and an ID field, where the attachments field contains the absolute reference to each file, and the ID field contains the record identifier the file is associated with? This would require either a table or some type of listbox on the form where the user would select the individual files from.

Thank you again,

Steve

The easiest way to create multiple attachments to a single Record is with a Child Table. A 1 to MANY Relationship will exist between the Parent and Child Tables via an ID Field (tblMain.[ID](1) ==> tblChild.ID([MANY]). The Attachments could then be viewed within a Sub-Form contained within the Main Form.

[Lebbsy]

Hint: By the use of a FileDialog Control, the process of adding *.pdf Files to existing Records will be greatly simplified. Simply select the proper File then write it to txtFilePath after which it can be saved to the underlying Table. If you need to know how to do this, please let me know.

Hello...

Reference is made to msg 11. I found this to be very much useful to what I have been trying to do for the past week and a half only that I want to add files of different file types to the database. I followed your instructions on that message and on trying to run the program I get the following error:
The expression On Current you entered as the event property setting produced the following error: Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of object modules.
* The expression may not result in the name of the macro, the name of the user-defined function, or [Event Procedure].
* There may have been an error on evaluating the function, event or macro.

In case of the above quote may I please ask you how that can be done.

Thanks.

[ADezii]

Hello...

Reference is made to msg 11. I found this to be very much useful to what I have been trying to do for the past week and a half only that I want to add files of different file types to the database. I followed your instructions on that message and on trying to run the program I get the following error:
The expression On Current you entered as the event property setting produced the following error: Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of object modules.
* The expression may not result in the name of the macro, the name of the user-defined function, or [Event Procedure].
* There may have been an error on evaluating the function, event or macro.

In case of the above quote may I please ask you how that can be done.

Thanks.

It could be 1 of 2 possibilities, the 1st of which would be a typographical error on my part and I apologize for this mistake. I am referring to Message #11, List Item #8:

If IsNull(Me![txtFilePath]) Then
  Me!cmdViewPDF.Enables = False
Else
  Me!cmdViewPDF.Enables = True
End If

-------------------- SHOULD BE ------------------

If IsNull(Me![txtFilePath]) Then
  'corrected line
  Me!cmdViewPDF.Enabled = False
Else
  'corrected line
  Me!cmdViewPDF.Enabled = True
End If

Let me know how you make out.

[Steve123]

The easiest way to create multiple attachments to a single Record is with a Child Table. A 1 to MANY Relationship will exist between the Parent and Child Tables via an ID Field (tblMain.[ID](1) ==> tblChild.ID([MANY]). The Attachments could then be viewed within a Sub-Form contained within the Main Form.

I created the relationships and the sub-form, and the application works as expected. However, I am having a little trouble with obtaining the replication ID for the record my main form is retrieving data from.

My form, Form1, contains a text box called Report_ID where I display the ID number from my table, called Table1, for the current record. The text box displays the report ID number as expected. However, in my VB code, when I try to use Me.Report_ID(1), I receive a number that does not seem to relate to the actual report ID in Table 1.

Is this the proper code to retrieve a replication ID from a form/table?

Thank you,

Steve

[ADezii]

I created the relationships and the sub-form, and the application works as expected. However, I am having a little trouble with obtaining the replication ID for the record my main form is retrieving data from.

My form, Form1, contains a text box called Report_ID where I display the ID number from my table, called Table1, for the current record. The text box displays the report ID number as expected. However, in my VB code, when I try to use Me.Report_ID(1), I receive a number that does not seem to relate to the actual report ID in Table 1.

Is this the proper code to retrieve a replication ID from a form/table?

Thank you,

Steve

when I try to use Me.Report_ID(1), I receive a number that does not seem to relate to the actual report ID in Table 1.

The (1) simply refers to the 1 side of a 1 to MANY relationship and should not be used in the actual coding. The reference should be:

Me.Report_ID

[Lebbsy]

It could be 1 of 2 possibilities, the 1st of which would be a typographical error on my part and I apologize for this mistake. I am referring to Message #11, List Item #8:

If IsNull(Me![txtFilePath]) Then
  Me!cmdViewPDF.Enables = False
Else
  Me!cmdViewPDF.Enables = True
End If

-------------------- SHOULD BE ------------------

If IsNull(Me![txtFilePath]) Then
  'corrected line
  Me!cmdViewPDF.Enabled = False
Else
  'corrected line
  Me!cmdViewPDF.Enabled = True
End If

Let me know how you make out.

I had already corrected that. Thank you for your help I have already found a solution to that. I hadnt divided the code between public module and form module. The code now works fine.

[ADezii]

I had already corrected that. Thank you for your help I have already found a solution to that. I hadnt divided the code between public module and form module. The code now works fine.

Glad it worked for you.

[Steve123]

The (1) simply refers to the 1 side of a 1 to MANY relationship and should not be used in the actual coding. The reference should be:

Me.Report_ID

ADezii,

That is what I thought, and tried originally, but when I use a message box to display Me.Report_ID, the string contains question marks. My VBA code is located in the Form1 class object, so I think the "Me" is referencing the form, which is then attached to the table where the replication IDs are located, so it should be able to retrieve the replication IDs. Do you know why this would not work?

Also, when I tried to use a direct reference to the replication ID that is located in Table1, as you suggested with tblMain.[ID] in post 13, I received an Object Required error. Why am I unable to access Table1 that Form1 is linked to directly from the Form1 class object?

Thank you again,

Steve

[Steve123]

ADezii,

I found that I need to use the function StringFromGUID() in order to properly retrieve the Report_ID number, since Access cannot display the GUID 16-byte array. I am now able to request files from the user, place copies of the files into a new folder that uses the Report_ID for the folder name, add the file names to an attachments table, and display the file names in a subform.

The only other thing I need help with is creating a link to the files that are in the new folder. I made the data type a "hyperlink" in my file name column, but I cannot follow the link when I click on it. I right clicked on the file name text and looked at the hyperlink, but there is no path or file name in the hyperlink text box.

Any suggestions?

Thank you,

Steve

[ADezii]

ADezii,

I found that I need to use the function StringFromGUID() in order to properly retrieve the Report_ID number, since Access cannot display the GUID 16-byte array. I am now able to request files from the user, place copies of the files into a new folder that uses the Report_ID for the folder name, add the file names to an attachments table, and display the file names in a subform.

The only other thing I need help with is creating a link to the files that are in the new folder. I made the data type a "hyperlink" in my file name column, but I cannot follow the link when I click on it. I right clicked on the file name text and looked at the hyperlink, but there is no path or file name in the hyperlink text box.

Any suggestions?

Thank you,

Steve

The only other thing I need help with is creating a link to the files that are in the new folder

I'm sorry Steve, but I thought that we were storing the Absolute Path to each File, and then opening them at will within Adobe via the ShellExecute() Function? Please clarify.

[Steve123]

I'm sorry Steve, but I thought that we were storing the Absolute Path to each File, and then opening them at will within Adobe via the ShellExecute() Function? Please clarify.

ADezii,

I am sorry for the confusion. I never clearly explained what I am actually doing. Essentially, I am making a user form where the user is allowed to "attach" files to a database record. However, I did not want my database to become too large, so I decided to save all of the attachments in a directory and then just make absolute references to the files, which is where your ShellExecute() function recommendation fit perfectly. Please see the following for a more detailed description.

I have a form with a button called Attachments. When the user clicks this button, they are presented with an msoFileDialogFilePicker to allow them to select files to attach to the record. The code then copies the file(s) they selected into a new directory. The code also places the new path name and name of each file into an attachments table. The main form then has a subform that displays the contents of the attachments table based upon the replication ID. I was unsure of the proper way to use the ShellExecute() function with multiple files in the subform, so I tried using a hyperlink instead.

Do you know of an easy way to access the files from the subform, using either hyperlinks or the ShellExecute() function?

Thank you,

Steve

[Steve123]

ADezii,

I was able to resolve my issue by using your ShellExecute() script in the OnClick event of my subform. I placed the following code in my subform class, where File_Name and File_Path are the names of columns in the table my subform is attached to:

Private Sub File_Name_Click()
    Success = Execute_Program(Me.File_Name, "", Me.File_Path)
End Sub
 

Thank you again for all of your help,

Steve

[EJaques]

@Steve123
Can you tell me how you copy the file into a new directory?

[ADezii]

@EJaques
The simplest way is via the FileCopy Statement, as in:

Dim strSource As String
Dim strDestination As String
 
strSource = "C:\Test\SomeFile.XXX"
strDestination = "C:\Windows\System32\SomeFile.XXX"
 
FileCopy strSource, strDestination