Copy and rename files from hyperlink

[Andy Weller]

Dear all,

Thanks Tom for the 'Hyperlink' answer to my original query. I am hoping
that someone can help me with a new problem?

I have a series of hyperlinks to specific (tif) files in Column A. What
I would like to achieve is an automated process that can:

1. Search all hyperlinks in Column A
2. Copy these files to a temporary folder (eg, C:\tmp), and
3. (In the copy process) rename these files to 1.tif, 2.tif, 3.tif, etc
(perhaps with a for loop).

I have (up to and including) 3199 Rows of data.

Hopefully that makes some sense. Any help would be greatly appreciated.

Andy

 

[Tom Ogilvy]

It might be easier use your original data


Assume as in the original, that the data is in columns A, B, C, D
Dim rng as range, cell as range
Dim sName as String
set rng = Range(cells(1,1),Cells(1,1).End(xldown))
for each cell in rng
sName = cell.Value & cell.Offset(0,1).Value & "_" & _
cell.offset(0,2).Value & "_" & cell.Offset(0,30.Value _
& ".tif"
filecopy sName, "C:\tmp\" & c.row & ".tif"
Next

Or,
Dim hlink as hyperlink
Dim i as long

i = 0
for each hlink in ActiveSheet.Hyperlinks
if hlink.rng.Column = 1 then
i = i + 1
filecopy hlink.Address, "C:\tmp\ & i & ".tif"
end if
Next

The above assume that hlink.Address will produce the fully qualified path
and filename for the file to copy. (I assume it will, but haven't tested
it).