Dir Exists Loop

[geospatial]

I currently have the following VBA code

Dim directory as string
directory = "C:\New Folder\Working Folder\"
 
If Dir(directory & "Jan2011.xls") <> = "" Then
  workbooks.Open fileName:=
    "C:\New Folder\Existing\jan2011.xls"
 
Else 
If Dir(directory & "Feb.xls") <> = "" Then
  workbooks.Open fileName:=
    "C:\New Folder\Existing\feb2011.xls"

This works fine except I now would like it to loop. If it finds jan2011.xls open it and then if it find feb2011.xls it also opens it and continues doing this until it doesnt find the specified file.

 

[Norie]

Do you have a list of files?

 

[geospatial]

I have a directory that has the files in it. For each month of the year a file is created so in August another file will be created called aug2011.xls. The way I do it now is copy the code from the previous month and then paste it and change the file name.

Never done a list of the file names and not sure what it would need to contain.

 

[Norie]

Do you actually want to loop through all the files in the directory?

 

[geospatial]

If thats the easiest way to do it. Only the files I will need will be in that given directory.

What I am really wanting it to do is for each workbook in directory open the workbook delete row 1 shift data up then copy the remaining data to an empty worksheet. Then close that workbook open the next delete row 1 shift data up, copy the remaining data and paste that below the data in the workbook that was just pasted and keep doing that until I hit all the files in the directory.

I have done this before, but it was all the same code copied and pasted over and over again for each month. Trying to narrow it down and make it a lot easier to manage.