Copy hyperlink content (.pdf) to a destination folder

[McQuinge]

Hi!

I have a sheet with several hyperlinked documents. With using a filter function I can get a list of documents that I want to print out.

So I need a macro to look at each hyperlink and find the filepath and name, copy the file to a destination folder like C:\test\
There is something with FileCopy but I dont know how to built it up in VB.

I have tried earlier to do a direct print of the hyperlink content, searched and started a thread, but I dont get any respons.

Please Help!

 

[Jon von der Heyden]

Hi

Here is one way, expects you to select the range of cells that contains the hyperlinks before running it:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] CopyFile()
    [COLOR="Blue"]Dim[/COLOR] objFSO [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] objFil [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] rngCell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Const[/COLOR] strNewDir [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR] = "C:\"
    
    [COLOR="Blue"]Set[/COLOR] objFSO = [COLOR="Blue"]CreateObject[/COLOR]("Scripting.FileSystemObject")
    
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngCell [COLOR="Blue"]In[/COLOR] Selection
            [COLOR="Blue"]Set[/COLOR] objFil = objFSO.GetFile(rngCell.Hyperlinks(1).Address)
            objFil.Copy strNewDir
        [COLOR="Blue"]Next[/COLOR] rngCell
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]GoTo[/COLOR] 0
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[McQuinge]

Thanks for the fast respons!

But I dont get any files to the destination folder.
No errors!

What to do?

 

[ScottR]

You must make sure you have a reference set to Microsoft Scripting Runtime in your VBA project.

 

[McQuinge]

Okey!

Maybe it's time to say that I don't know how to use VB?
What do you mean by having a reference to Microsoft Scripting Runtime?

 

[ScottR]

In your VBA window and in the top menu go to Tools=>References

A list of available references will appear (code that integrates other "libraries" to your Excel Project).

Scroll down until you reach Microsoft Scripting Runtime. Tick it and click OK.

Bob's your mother's brother!

 

[Jon von der Heyden]

Hi

This is late bound so no need to set the reference.

Are the hyperlinks in your sheet valid? I am assuming that you have used hyperlinks, they are usually blue underlined that open the file when you click on it. Is that the case in your worksheet?

 

[McQuinge]

Yes it is!
I have made a test sheet just to be sure that nothing else is interfering.
I have normal hyperlinks made by right clicking the cell and selecting hyperlink.
I have also tried the hyperlink function.

All my hyperlinks addresses are with only the filename, meaning that they are placed in the same folder as the excel sheet.
Could that be a problem?
A address of a hyperlink would be: TEST1.pdf

 

[Jon von der Heyden]

Does this work:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] CopyFile()
    [COLOR="Blue"]Dim[/COLOR] objFSO [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] objFil [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] rngCell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] strOldDir [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Const[/COLOR] strNewDir [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR] = "C:\"
    
    strOldDir = ThisWorkbook.Path & "\"
    
    [COLOR="Blue"]Set[/COLOR] objFSO = [COLOR="Blue"]CreateObject[/COLOR]("Scripting.FileSystemObject")
    
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngCell [COLOR="Blue"]In[/COLOR] Selection
            [COLOR="Blue"]Set[/COLOR] objFil = objFSO.GetFile(strOldDir & rngCell.Hyperlinks(1).Address)
            objFil.Copy strNewDir
        [COLOR="Blue"]Next[/COLOR] rngCell
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]GoTo[/COLOR] 0
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[McQuinge]

Yes! This was much better.
Now I just mark what hyperlinks I want to copy and they ends in a destination folder.
But It seems like when I mark a range from row 2 to 15, I get the file from each row. some of the rows are hidden because of the filter, but the macro finds them anyway.

Sorry if I have explained something wrong here.