Solved: Renaming folder files

[Jow]

I've got a task that I need to complete, it may be difficult or may turn out to be really simple :p

What I have is a folder with a number of excel files and a number of pdf files with the same name, for example..


file1.xls
file1.pdf
file2.xls
file2.pdf
file3.xls
file3.pdf

Now, within each excel file (always in cell A1) there is a number.

What I want to do is write a macro that will open up each excel file within the folder in turn, and rename the pdf of the same name with the number in cell A1. And also move the renamed PDF into a new folder (would be a fixed directory).

So the macro will open up file1.xls, and assuming the value of cell A1 was '7345', it will then rename file1.pdf as '7345' and move it into a folder called "renamed pdf files".
And continue to do the same thing for file2.xls and file3.xls etc

This is for a pretty big job of about 300 pdf files so I'd really appreciate as much help as I can get, I am familiar with writing simple excel macros and i understand more complicated code enough to edit it and make changes, but this is something I'm not really able to even begin myself.

I can also make a donation if someone is prepared to guide me through it, although any help I can get would be great :)

[Bob Phillips]

Sub RenameFiles()
Dim mpFile As String
Dim mpWB As Workbook

mpFile = Dir("C:\test\*.xls", vbNormal)

Do While mpFile <> ""

Set mpWB = Workbooks.Open("C:\test\" & mpFile)
Name Replace(mpWB.FullName, ".xls", ".pdf") As _
"C:\renamed pdf files\" & mpWB.Worksheets(1).Range("A1").Value & ".pdf"
mpWB.Close savechanges:=False
mpFile = Dir
Loop
End Sub

[Jow]

Wow, worked perfectly :)

Now I see it written down it looks obvious, but for some reason I can never start these things from scratch, guess that will come in time.

Thanks alot xld, thats turned a big job into a single button click :)