Saving files in TIF format - VBA

[PATSYS]

Hi all,

In Sheet1 column A, I have listed all the files that can be found in "C:\Documents\"

Then I have the following code:

 Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
 ToPath = "C:\Documents\"

 For k = 1 To x
 On Error Resume Next
 Name ToPath & Sheets("Sheet1").Cells(k, 1).Value & ".TIF" As ToPath & 1000 + k * 10 & "   " & Sheets("Sheet1").Cells(k, 1).Value & ".TIF"
 Next k

That code renames the files by adding a string (1000 + k*10) to the filename.

This code works well, except if one of the files are in format other than TIF. The only other format in the folder is PDF, so it could only either be TIF or PDF.

So my question is,. how do I change above in such a way that it should just use whatever is the file extention of the file in the folder?

Thanks in advance.

 

[Scott Huish]

You can do this without listing the files in Column A, but since you have, why not just check the extension?

BTW, you have not defined a value for x anywhere in your code.

If Right(Cells(k,1),4)=".TIF" then Name ToPath & Sheets("Sheet1").Cells(k, 1).Value & ".TIF" As ToPath & 1000 + k * 10 & "   " & Sheets("Sheet1").Cells(k, 1).Value & ".TIF"

 

[PATSYS]

You can do this without listing the files in Column A, but since you have, why not just check the extension?

BTW, you have not defined a value for x anywhere in your code.

If Right(Cells(k,1),4)=".TIF" then Name ToPath & Sheets("Sheet1").Cells(k, 1).Value & ".TIF" As ToPath & 1000 + k * 10 & "   " & Sheets("Sheet1").Cells(k, 1).Value & ".TIF"

Hi Hotpepper,

Thanks

I am still relatively new to VBA so these "obvious" solutions are not too obvious for me

About x, I accidentally copped my code, x is defined as:

x=Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row