VBA to move PDF files to Directory

User avatar
Joseph
3StarLounger
Posts: 206
Joined: 31 Dec 2010, 22:23
Location: Columbia Falls, MT

VBA to move PDF files to Directory

Post by Joseph »

I had this one land on my desk today. I need to loop through a list of file paths in column "A" that are set up as hyperlinks. I need to move those files to a static directory. However when I try the code below, I get a "File not found" error. Agnone have something for this?


Here is what i was messing with to no avail:

Code: Select all

Sub RectangleRoundedCorners1_Click()
    Dim PDFfile As Range
    For Each PDFfile In ActiveSheet.Range("A1:A300")
        If PDFfile.Value <> "" Then
            FileCopy PDFfile.Value, "C:\Users\JValentine\Documents\Test" & PDFfile.Value
        End If
    Next
End Sub
The hyperlinked file pathsin column "A" look like: \\grouper\IBML\Images\060523\742200\09761901652.pdf              

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to move PDF files to Directory

Post by HansV »

The values in column A already contain a path. You only want to use the filename in the target.

Code: Select all

Sub RectangleRoundedCorners1_Click()
    Dim PDFfile As Range
    Dim Filename As String
    For Each PDFfile In ActiveSheet.Range("A1:A300")
        If PDFfile.Value <> "" Then
            Filename = Mid(PDFfile.Value, InStrRev(PDFfile, "\") + 1)
            FileCopy PDFfile.Value, "C:\Users\JValentine\Documents\Test\" & Filename
        End If
    Next
End Sub
Best wishes,
Hans

User avatar
Joseph
3StarLounger
Posts: 206
Joined: 31 Dec 2010, 22:23
Location: Columbia Falls, MT

Re: VBA to move PDF files to Directory

Post by Joseph »

Thanks Hans, it's been awhile, hope you are well!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to move PDF files to Directory

Post by HansV »

I'm fine, I hope you are too.
Best wishes,
Hans