VBA Help

[jtm311]

Here is my project. I have an excel list of part numbers (PDF forms, the forms are also name after the P/N) which I need to search on my PC's files if found copy or move them to a new location then have an option to later delete them form their old location.

Easy right? Any help would be great.

John

 

[SkipVought]

Hi,

What code to you have so far and where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

And what version are you using. If it is BEFORE 2007, you can use FileSearch (which makes it fairly easy). If you are using 2007 or 2010 you can not use FileSearch.


unknown

 

[jtm311]

Ok I'm using 2007.

I'm starting with two of the basic code which do work. I'm stuck with the following, How to search for the number in the column to the left? Then what do I change in the code to search for that number in a drive like z:\ and not just a path like I call out below.

Hope this makes sence?
John

Sub FileExists()

End Sub
Dim fso
Dim file As String
file = "z:\coa\pdf\54789.pdf"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
MsgBox file & " was not located.", vbInformation, "File Not Found"
Else
MsgBox file & " has been located.", vbInformation, "File Found"
End If
End Sub


Sub CopyFile()

End Sub
Dim fso
Dim file As String, sfol As String, dfol As String
file = "54789.pdf" '
sfol = "z:\"
fol = "c:\COA Docs\"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & file) Then
MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not fso.FileExists(dfol & file) Then
fso.CopyFile (sfol & file), dfol
Else
MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
End If
End Sub

 

[SkipVought]

Your list of P/Ns are in WHAT RANGE?

"How to search for the number in the column to the left? To the left of WHAT?

Please post an example of the data you have on your sheet and explain what you want to do with it.

Please answer ALL questions posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jtm311]

My part number are from C4 - C56
Part Number lot number
71219611 256
71219612 266
71219613 288
71219625 299
71219635 310
71219665 333
I want to run the code on the Part number column "C" go find the related document (71219611.pdf example) which is in my Z: drive, once found, copy it to the new location C:\COA\Docs\. Then at a later time run a new code to go back to the Z:\ find it again and delete it.

Hope this helps,
Thanks,
John

 

[SkipVought]

You never stated what lotnumber is all about.

Check out DeleteFile in VBA Help

Sub CopyFile()
'object.CopyFile ( source, destination[, overwrite] )
    Dim fso, r As Range
    Dim sfol As String, dfol As String
    sfol = "z:\"
    dfol = "c:\COA Docs\"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    On Error Resume Next
    
    For Each r In Range(Cells(4, "C"), Cells(4, "C").End(xlDown))
        fso.CopyFile sfol & r.Value & ".pdf", dfol & r.Value & ".pdf"
        If Err.Number <> 0 Then
            r.Offset(0, 2).Value = Err.Number
            r.Offset(0, 3).Value = Err.Description
            Err.Clear
        End If
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jges]

Then what do I change in the code to search for that number in a drive like z:\ and not just a path like I call out

Does this mean your files are not in a specific folder on the Z drive, but rather you have to search the entire Z drive to find the specific file?

 

[jtm311]

Sorry the lot is also a feild I brought into the excel file as a check, however, really does not play into what I need.
I will try the delete one also.

 

[jtm311]

Jges this is correct, that is part of my problem I do not know how to write the code to search the Z: drive. I only know what I posted above.

John

 

[SkipVought]

Check out the SubFolder method of the FileSyetemObject.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}