How Can The Macro Rename Files When The Original File Name Changes Everday

Hi I'm quite new to Excel VBA so I'm having a bit of trouble with the codes that Excel uses but I know the overall steps of my program and the logic behind it. I would be greatful if someone could guide me on this.

- Suppose I have a folder with a list of 3 files, updated daily.

- The 3 files are updated daily and named according to the current date. So for example, if it was Tuesday 28 July, the three files would be named:
HOJ_20080728.xls
UUL_20080728.xls
QWK_20080728.xls

If the date was September 29 2008 the files would be named:
HOJ_20080929.xls
UUL_20080929.xls
QWK_20080929.xls

and so on..


- Now, I need my program to convert the files to a different name:
For the "HOJ_date" file, the name needs to change to H11A.csv
For the "UUL_date" file, the name needs to change to U22A.csv
For the "QWK_date" file, the name needs to change 2 Q33A.csv

This may seem easy but the problem is that the name of the original files change everyday because of the date.

So I need the program to be able to change the file names to the ones I specified above everday, regardless of the fact that the date changes.

I kind of have a "half-solution" but I'm not too familiar with the VBA terminology for codes so I will just list the overall kind of logic.
-------------------------------------------------------------------------
Suppose today is 15 March 2008, then:

-------------------------------------------------------------------------

\\ specify the day string
Dim Day As String;

\\ specify the day
Day = "20080315"

\\ now check the directory for all the files. Open them one by one and 
change their file names and save as a new CSV file.

Open file "C:\HOJ_" & Day & ".xls"
        SaveAs Filename:="H11A.csv", FileFormat:=xlCSVMSDOS
Close file

Open file "C:\UUL_" & Day & ".xls"
        SaveAs Filename:="U22A.csv", FileFormat:=xlCSVMSDOS
Close file

Open file "C:\QWK_" & Day & ".xls"
        SaveAs Filename:="Q33A.csv", FileFormat:=xlCSVMSDOS
Close file

-------------------------------------------------------------------------

So that's the overall logic I wanted to implement.
The program gets all the files from the folder and opens them one by one and saves a CSV version with new names.

Do you think this may be possible?

Please do help me.

- The only hard thing I reckon is the fact that I don't know whether you can just open any file from the folder, in any order you want and then change the filename. And then open any other file you wish. Do you see what I mean?

I would greatly appreciate your help.

Many thanks.

P.S this is getting quite long for me so if someone could get a solution then I would be over the moon and really greatful to you!

Hi,

This is untested - (which I usually like to do before posting) since it uses the File System object, and unfortunately the version of Excel for the Mac, which I'm using at the moment, does not support this syntax. However I hope it gives you the general idea.

Sub ChangeFileNames()
Dim fso, f, fs, f1
Dim wbkNew As Workbook
Dim Day As String;
Dim stFName as String


 Set fso = CreateObject("Scripting.FileSystemObject")
 Set f = fso.getfolder("path and folder string for your files")
 Set fs = f.Files
 For Each f1 In fs
    If Right(f1.Name, 3) = "xls" Then
          Set wbkNew = Workbooks.Open("path and folder string for your files & f1.Name)
          With wbkNew  

             Select Case Left(wbNew.Name,3)

               Case = "HOJ"
                   stFname="H11A.csv"
               Case = "UUL"
                   stFname="U22A.csv"
               Case = "QWK"
                   stFname="Q33A.csv"

              End Select

              .SaveAs Filename:="path and folder string for your files"  & stFName, FileFormat:=xlCSVMSDOS
              .Close False
          End With
    End If
 Next
End Sub