Faster way to save a list of hyperlinked files to a specfic folder

[skinnerq]

I have a spread sheet that has a colunm of hyperlinked files that I have filtered. I now want to save the hyperlink files I have filtered into a specific folder but It is taking very long because I need to select the hyperlink then the pdf opens, then I save the pdf to a folder and then go to the next hyperlink just below and continue.

I there a faster way to do this. PLEASE HELP!!!

 

[tweedle]

Hi;

Possibly the below will get you started;
This utillizes File Scripting to perform the copy; no opening needed.

Sub CopyLinkedFiles()
' Copies Hyperlinked Files
' Tweedle
'---Requires Reference to Microsoft Scripting Runtime
'-----IDE Menu Tools / References: Check the Microsoft Scripting Runtime
Dim aws As Worksheet
Dim fso As New FileSystemObject 'Creates an instance of FSO
Dim oFile As File
Dim hl As Hyperlink
Dim targetPath$
'Where files are copied to
targetPath$ = "H:\Temp\"    'UPDATE as needed
 
'Sets the worksheet to work with
Set aws = ActiveSheet
'Alternate Forms
'Set aws = Sheets(1)
'Set aws = Sheets("FileList")
'Loop through Hyperlinks of the sheet
For Each hl In aws.Hyperlinks
 
    'Identify the cell in which the Hyperlink resides
    Set C = Range(hl.Range.Address)
    'Test that it is not filtered
    If C.Rows(1).RowHeight > 0 Then
 
        'Set the File object, then
        Set oFile = fso.GetFile(hl.Address)
        'use the File object to copy the file
        oFile.Copy targetPath$, [U]OverWriteFiles:=True[/U]
    End If
Next hl
End Sub

 

[skinnerq]

But were do I put this code in excel

 

[tweedle]

So when you said

It is taking very long because I need to select the hyperlink

you are literally clicking on the hyperlink ...
I somehow interpretted that as code/macro was performing this action.

But were do I put this code in excel

This code will run from a standard module.

Check this link http://www.contextures.com/xlvba01.html
on how to add a module and paste the code

Once you get that part accomplished;
you'll need to perform 2 additional tasks;

Add the reference to th eMicrosoft Scripting Runtime
'---Requires Reference to Microsoft Scripting Runtime
'-----ALT+F11: Menu:Tools / References: Check the Microsoft Scripting Runtime from the list

and...
update this in the code to the path where the files should copy to:
targetPath$ = "H:\Temp\" 'UPDATE as needed

 

[skinnerq]

Can I call you or can you call me? I am trying to do this for work and am having trouble getting it to work. Thank!

 

[skinnerq]

Sorry but I still can't get the code to work...Is there any other way you can help??? Thanks!

 

[tweedle]

Are you receiving error messages?
If so what are they?

 

[skinnerq]

I inserted the code into the VBA box and then I went to select the marco to run it. the marco i choose was called Module2.CopyLinkedFiles. When I ran that a box came up saying compile error: user-defined type not defined. I press ok and then it goes to the code box and high lights sub copylinked files (). ????? not sure what this means. I can call you if that is easier?

 

[tweedle]

Did you perform these two steps:
========================
Add the reference to th eMicrosoft Scripting Runtime
'---Requires Reference to Microsoft Scripting Runtime
'-----ALT+F11: Menu:Tools / References: Check the Microsoft Scripting Runtime from the list
[ensure there is a checkmark ]--------------^


and...
update this in the code to the path where the files should copy to:
targetPath$ = "H:\Temp\" 'UPDATE as needed <!-- / message --><!-- sig -->

 

[skinnerq]

Yes I did