Excel Macro Transfer Docs Into Folder From List

S

sskicker23

Joined
Jan 12, 2010
Messages
2
Reaction score
0
hello,

So my problem is that I have an extensive list of file names, minus the extensions, in an excel document in column A. I currently have to take each file name and search through a folder on my hard drive, c:files, then copy the file with the matching name into a new folder, c: correct folder.

Is there a code that could assist me in this project. Im fairly new to vba and just breaking the ground with some of its amazing potential.

If my request is not clear please let me know

Thanks a million

sskicker
 
Hello sskicker23. Try this code.
Code: 
Sub Macros1() 'of Module1
 On Error GoTo err1
    
 	Dim xls_inp As String, xls_out As String
    
 	Dim fso
 	Set fso = CreateObject("Scripting.FileSystemObject")
    
 	Dim rngA As Range, rng As Range
 	Set rngA = ThisWorkbook.Worksheets("Sheet1").[b]Range("A2:A100")[/b]
    
 	Dim i As Long, j As Long
 	For Each rng In rngA
 		If Not IsEmpty(rng.Value) Then
 	   
 			xls_file = Trim("" & rng.Value) & ".xls"
 		   
 			xls_inp = "C:\__WHERE FROM__\" & xls_file
 			xls_out = "C:\correct folder\" & xls_file
 		   
 			fso.[b]CopyFile [/b]xls_inp, xls_out, True
 			i = i + 1
 		Else
 			j = j + 1
 		End If
 	Next
    
 	Set rngA = Nothing
 	Set rng = Nothing
 	Set fso = Nothing
    
 	MsgBox "copied " & i & " files" & vbCrLf & "missing " & j & " cells"
    
 	Exit Sub
 err1:
 	MsgBox Err.Description
 End Sub
 
The code

Thank you very much for the quick response.


ok, so ive played around with the code a little bit and Im having two problems. The first one is that it will only work if there is 1 item or file name in column A. Once I put in a second or third file name in column A. A prompt comes up saying "file not found"

The second is the file extension. In the excel document I can add the extension to the file name to forgo a little bit of the code.

Please let me know what you think
 
Can you show me what was written in the column "A". Is it possible?
a few rows

  1. ?????
  2. ????
  4. ??????
 
Back
Top