UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Copy Hyperlinked Pdf Files, Access 2016    
 
   
hubelea
post Apr 21 2017, 07:23 AM
Post#21



Posts: 18
Joined: 19-April 17



"Just Set the Directory Path as Variable"....not sure what that means. Based on what I got out of your comments that I can't use a hyperlink to copy a file, I added a text box control that is not formatted as a hyperlink, and just put the path to the document in the field. Then I tried this code:

Private Sub CopyLabelsCmd_Click()
Source = Forms!HyperlinkSampleFrm!PDFHyperlinkText
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"
FileCopy Source, Destination
End Sub

I'm still getting 'path not found'
Go to the top of the page
 
hubelea
post Apr 21 2017, 07:41 AM
Post#22



Posts: 18
Joined: 19-April 17



Background on what I am trying to accomplish. I use my database to track products to submit for government approval. Products need to meet certain requirements, which vary by state, so it is a process to go through thousands of products, narrow down to those being sold within a specific state, and then that meet state requirements, and aren't already approved. Access does a great job in helping me to do this. So, on this final continuous form, I have a listing of products that I need to submit. Sometimes its a couple of hundred products. Then it becomes a manual process to go into my file directories and select the product labels (pdfs) that I need to submit, copy them, and paste them into a separate folder so that I can upload the folder to an FTP site.

PDFs are stored in separate folders based on the retailer selling the product (these are all store brands). So all of Kroger's product pdfs are in a Kroger folder, etc. The path to each product is different. But the final folder that I need to copy them to will be consistently the same.

It would be 'nice' if I could use a hyperlink to the label to copy the label to this folder, because then I would also have the ability to quickly look at the label for nutritional info if I needed to. But if a hyperlink is indeed not 'copyable' in access, it would still be helpful if I stored the path to each product in a field, contained in a textbox control on my continuous form, and Access would then help me to copy all of the files on that form into the one folder.

Right now I have a command button on the continuous form with this code. (Ignore the reference to hyperlink in the names; based on your input the control 'pdfhyperlinktext' is a text box control containing the path to the pdf, example of that path is: C:\Users\Anne\Dropbox\Account Specific\Kroger\0 11110 01835 9 Kroger White Grape Jc 64oz.pdf

Private Sub CopyLabelsCmd_Click()
Source = Forms!HyperlinkSampleFrm!PDFHyperlinkText
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"
FileCopy Source, Destination
End Sub

This is giving me 'path not found'.
Go to the top of the page
 
River59
post Apr 21 2017, 07:43 AM
Post#23



Posts: 1,172
Joined: 7-April 10
From: Detroit, MI


Moke123 has been giving you lots of ideas for this. You can open the hyperlink, then save the document it where you want. That might be a lot easier than what you are attempting to do. Like we both have said, you would have to 'strip out' the document file name from the hyperlink, which in most cases cannot be done. If the documents are all stored in one location, then just identify the document that you want and copy from there to your destination.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
hubelea
post Apr 21 2017, 07:57 AM
Post#24



Posts: 18
Joined: 19-April 17



Yes, I really appreciate all of the ideas! I keep trying various methods, and I'm sure my lack of experience is making this more difficult than it seems it should be. I'm hoping that opening each individual hyperlink and then doing a 'save as' in Adobe for the pdf isn't the final solution, this continuous form sometimes has hundreds of products. I was hoping that using filecopy would do this for all of the individual pdfs on the continuous form, by clicking the command button.

Is there a solution that has been suggested that I am missing? Please, let me know if there is?
Go to the top of the page
 
moke123
post Apr 21 2017, 08:16 AM
Post#25



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


QUOTE
Right now I have a command button on the continuous form with this code.

is this button on each line of the continuous form?

QUOTE
It would be 'nice' if I could use a hyperlink to the label to copy the label to this folder, because then I would also have the ability to quickly look at the label for nutritional info if I needed to.

that is what the shellexec module is for. (You can also use the application.followhyperlink method)

if you upload a copy of your db we could probably sort this out pretty quickly.
This post has been edited by moke123: Apr 21 2017, 08:19 AM
Go to the top of the page
 
hubelea
post Apr 21 2017, 08:27 AM
Post#26



Posts: 18
Joined: 19-April 17



There is one command button, intention is to use it to copy all files selected by the underlying query and displayed on the form.

What is the code I need to put behind this button?
Go to the top of the page
 
moke123
post Apr 21 2017, 08:35 AM
Post#27



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


i think you'd probably need a recordset and loop through the recordset.

just to experiment put a button on each line of the continuous form.

under the click event put

CODE
Source =Me.PDFHyperlinkText (if the name of the control is PDFHyperlinkText)
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"
FileCopy Source, Destination


see what happens and post back

Go to the top of the page
 
hubelea
post Apr 21 2017, 08:54 AM
Post#28



Posts: 18
Joined: 19-April 17



Added a button on the line, used the code you provided, got 'path not found'. It is highlighting in yellow the last line of the code, Filecopy Source, Destination.
Go to the top of the page
 
River59
post Apr 21 2017, 09:34 AM
Post#29



Posts: 1,172
Joined: 7-April 10
From: Detroit, MI


I don't think that you are giving us all of the information that we need to help, hubelea. In Post #12, you show the source hyperlink as:

..\Account Specific\Kroger\0 11110 01834 2 Kroger 100pct Grape Jc 64oz.pdf#..\Account%20Specific\Kroger\0%2011110%2001834%202%20Kroger%20100pct%20Grape%20Jc%2064oz.pdf#

This does not indicate to me that this is a flat file sitting on your hard drive as you are indicating. Without knowing exactly what you are dealing with, I cannot offer any further help. Perhaps someone else here has the time to work with you.
I hope that you find an answer.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
moke123
post Apr 21 2017, 09:38 AM
Post#30



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


do you have option explicit declared at the top of the module?
CODE
Option Compare Database
Option Explicit


try this
CODE
dim Source as string
dim Destination as string

Source =Me.PDFHyperlinkText (if the name of the control is PDFHyperlinkText)

debug.print "Source = " & Source

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"

debug.print "Destination = " &  Destination

FileCopy Source, Destination



copy and paste back the results of the debug.print from the immediate window so we can see it
Go to the top of the page
 
hubelea
post Apr 21 2017, 09:52 AM
Post#31



Posts: 18
Joined: 19-April 17



Added Option Explicit

Used this code:
Private Sub CopyLabelsCmd_Click()
Dim Source As String
Dim Destination As String

Source = Me.PDFHyperlinkText

Debug.Print "Source = " & Source

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"

Debug.Print "Destination = " & Destination

FileCopy Source, Destination
End Sub

Got Error 76 - Path Not Found

Immediate Window Printed this (and it looks like it found that pdf I was pointing to:)

Source = C:\Users\Anne\Dropbox\Account Specific\Kroger\0 11110 01835 9 Kroger White Grape Jc 64oz.pdf
Destination = C:\Users\Anne\Dropbox\SubmissionPDFs\


Is there a possibility that the windows (Windows 8) path is not reading correctly? I did go to both the source and destination, shift/right clicked and copied the path, and pasted it directly into the code.

Go to the top of the page
 
moke123
post Apr 21 2017, 10:42 AM
Post#32



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


did you check that both those paths actually exist? they look correct.
do you have a folder SubmissionPDFs?
Go to the top of the page
 
hubelea
post Apr 21 2017, 11:07 AM
Post#33



Posts: 18
Joined: 19-April 17



Progress!!! Actually got a file to copy to the destination folder! It appears that I had to give the new file a name. Still need help, tho. Here's the code I'm using:

Option Compare Database
Option Explicit
Private Sub CopyLabelsCmd_Click()
Dim Source As String
Dim Destination As String
Source = Me.PDFHyperlinkText
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"
FileCopy Source, Destination & "Me.HyperlinkText" & ".pdf"
End Sub

Two problems 1) Its only saving one of the pdfs
2) Its naming it Me.HyperlinkText.pdf (which is not surprising, since that's what I am telling it to do)

Problem 1 may be due to problem 2, since they all will have the same name with this code. Can you help me figure out how to name them all differently? I'd ideally like to keep their original names.
Go to the top of the page
 
moke123
post Apr 21 2017, 12:48 PM
Post#34



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


oops didnt notice before that you werent passing a full destination string.

CODE
dim Source as string
dim Destination as string
dim strName as string

Source =Me.PDFHyperlinkText (if the name of the control is PDFHyperlinkText)

strName = Mid(Source, InStrRev(Source, "\") + 1)

debug.print "Source = " & Source

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\" & strName

debug.print "Destination = " &  Destination

FileCopy Source, Destination


try this. i added the name to the end of the destination
Go to the top of the page
 
moke123
post Apr 21 2017, 01:04 PM
Post#35



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


in order to do them all at once you can create a recordset of all the files you want to copy and pass the file path to a sub.

your sub could be something like this..

CODE
Public Sub CopyMyPdf(strFile As String)
Dim Source As String
Dim Destination As String
Dim strName As String

Source = strFile

strName = Mid(Source, InStrRev(Source, "\") + 1)

Debug.Print "Source = " & Source

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\" & strName

Debug.Print "Destination = " & Destination

FileCopy Source, Destination

End Sub


and then with your recordset you would do something like

CODE
Do Until rs.EOF
CopyMyPdf (rs!PDFHyperlinkText)
rs.MoveNext
Loop
Go to the top of the page
 
hubelea
post Apr 21 2017, 02:19 PM
Post#36



Posts: 18
Joined: 19-April 17



This is where I am right now. Still only copying one file, my loop apparently isn't correct. (I wanted to be sure I was looping only through the forms recordset and not the table or query underlying the continuous form).

Private Sub CopyPDFcmd_Click()
Dim rst As DAO.Recordset
Dim Source As String
Dim Destination As String
Dim NewFileName As String

Set rst = Forms!HyperlinkSampleFrm.RecordsetClone
Source = Me.PDFHyperlinkText

Debug.Print "Source = " & Source

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\"

Debug.Print "Destination = " & Destination

NewFileName = Me.UPC & " " & Me.BrandLookup & " " & Me.Descr & " " & Me.Size

Debug.Print "NewFileName = " & NewFileName

rst.MoveFirst
Do Until rst.EOF
FileCopy Source, Destination & NewFileName & ".pdf"
rst.MoveNext
Loop
End Sub
Go to the top of the page
 
jwhite
post Apr 21 2017, 03:43 PM
Post#37


UtterAccess VIP
Posts: 5,801
Joined: 31-August 06
From: North Carolina, USA


I realize this thread is quite long and kind of late to suggest this, but...

Instead of copying static files all over the place, why not have a table with the file names, say tblProductAttachments (with ProductID as FK and name of PDF files, 1 per record) as a child table of tblProducts. Then you can easily pull the PDFs for printing or whatever anytime later based on the ProductID's, and not have a gazillion duplicate files all over the place.

--------------------
Go to the top of the page
 
moke123
post Apr 22 2017, 07:53 AM
Post#38



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


try this

place a button in the header of your form with the following in the onclick event

CODE
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

Do Until rs.EOF

CopyMyPdf (rs!PDFHyperlinkText)

rs.MoveNext
Loop

set rs = nothing


then seperately place the following after the end sub of the above code (these are 2 seperate procedures)

CODE
Private Sub CopyMyPdf(strFile As String)
Dim Source As String
Dim Destination As String
Dim strName As String

Source = strFile

strName = Mid(Source, InStrRev(Source, "\") + 1)

Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\" & strName

FileCopy Source, Destination

End Sub


the first procedure will loop thru your forms recordset and send PDFHyperlinkText to the second procedure which then copies the pdf to your destination.
this uses your original file name. if you are going to change the name (which was not what you originally wanted) we will have to make some changes and pass that as another argument.
This post has been edited by moke123: Apr 22 2017, 07:54 AM
Go to the top of the page
 
hubelea
post Apr 22 2017, 02:43 PM
Post#39



Posts: 18
Joined: 19-April 17



Moke123, you are the greatest wizard in the history of Access! So simple, but it works like a charm! Thank you so much, I really appreciate you hanging with me on this!

Now I'm going to go back and see if I can duplicate that 'file picker' and get all of these file locations set up - I only have a 20,000 of these pdfs, so this should be interesting! But, in the long run, what a huge timesaver this will be to not have to go find each file individually every time I need to do a government submission.

Thank you again!
Go to the top of the page
 
moke123
post Apr 22 2017, 03:32 PM
Post#40



Posts: 1,010
Joined: 26-December 12
From: Western Ma., USA


your welcome.

if you have 20,000 pdfs you may want to re-read jwhites suggestion. Although i really dont know how you have your data set up there are probably easier ways of importing all those file paths than one at a time with fileDialog. You may want to
check out Allen Browns List Files to Table Procedure
If all your paths look like C:\Users\Anne\Dropbox\Account Specific\Kroger\0 11110 01835 9 Kroger White Grape Jc 64oz.pdf, you may be able to parse out the strings to match them with your other data.

Good luck with your project.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    24th July 2017 - 05:25 AM