search for a file in a folder using rows of data in an excel file

[venkitaraman]

Hello Everyone,
i am doing a process manually which is very hectic and need to get it automated.
the process is as described as below :

1)i have an excel file where there are 200 rows of data say from A5 to A205. i have to manually copy the data from each row and use the data as a search string and search for a corresponding file in a folder.

2)i have to copy the search result and paste it another folder.

please help

regards

Venkitaraman

[mdmackillop]

Hi venkitaraman,
Welcome to VBAX.
To rephrase,
You want to copy all the files named in a list from one folder to another?

[venkitaraman]

Hello sir,
thank you for replying.
let me elobrate my problem.

i have some vaalues inan excel sheet in 1 row. for example , from cell number A5 to A200.
first i copy the value in the cell A5 , take the copied value as a search string and search for a file in a folder.
i have to copy the search result and paste in another folder.
hope i have made my self clear.
let me give an example.........

1) i have an excel file named 'sheet1', in which i have values only in 1 row, say A5 to A200.
2) i will copy the value of cell A5 which is a number say '55555'.
3)i will use this value to search for a file in a folder say 'c:\venki'.
4)i will copy the search result to another folder.
5)then i will copy the value from 'A6' and do the step 3 and 4. similiraly i will repeat for 'A7','A8',........,'A200'.

I WANT THIS PROCESS TO BE AUTOMATED.

Regards
Venkitaraman

[mdmackillop]

2) i will copy the value of cell A5 which is a number say '55555'.
3)i will use this value to search for a file in a folder say 'c:\venki'.

Is the file called 55555.xls or are you tying to search for this value as data within the files?

[venkitaraman]

hi,
its not 55555.xls, '55555' is just the value in row number A5.....
i will copy the value '55555' from the row number, and use this value as a search creteria to search for a corresponding file in a folder and then copy the search result and put it in another folder.


regards
Vernkitaraman

[Bob Phillips]

Is it not just



LastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 5 To LastRow
sFile = Dir("C:\test\" & Cells(i,"A").Value & ".*")
If sFile <> "" Then
Name sFile "C\other folder\" & Cells(i,"A").Value & Right(sFile,4)
End If
Next i

[venkitaraman]

hi xld,

iam getting a compile error while running the code on visual basic editor for that excel file.


regards
Venkitaraman

[venkitaraman]

hi xld,
now iam not getting any error ...what i did...
Name sFile(just add 'as' here) "C\other folder\" & Cells(i,"A").Value & Right(sFile,4)
but iam not sure if this code work the way i want...

let me be more accurate...
can you plese tell me how to code a search function which will take the value from a row say A5 and display the search result.

lets accomplish the above mentioned task first

regards
Venkitaraman