Opening files from a variable (todays' date) folder name

[Hari]

Hi,

I got the code for opening all files within a particule folder yesterday
from William through NG.

When I wrote to William I had the requirement for opening all the Excel
files within the folder "c:\ccapps\ttlview\tmp\" but while fitting it with
other parts of the code I find that I need to open all the Excel files one
step down from "tmp" folder also. That step down folder will be determined
from today's date, formatted as "dd-mmm-yy".

I modified his code to the following one :-

Sub savingasnewname()
With Application.FileSearch
.NewSearch
p = Format(Now, "dd-mmm-yy")
.LookIn = ("c:\ccapps\ttlview\tmp\" & p)
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
'MsgBox "Hi"
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Next i
End With

End sub

I thought that today being 10-Jun-04 I will get all the excel files within
folder "c:\ccapps\ttlview\tmp\10-Jun-04" bring opened, but the code wasnt
executing anything. I introduced a message box statement to know if the code
was going inside the "for loop" but it is not.

I understand that the problem is in ".Lookin" statement where Im combining a
constant and a variable. Is there any way I may resolve the same.

( The folder by today's date would always exist because this code is the
last in a sequence of codes and the previous codes have already created
folder with name being today's date.)

Regards,
Hari
India

 

[William]

Untested, but try.......

..LookIn = "c:\ccapps\ttlview\tmp\" & Format(Now, "dd-mmm-yy")

--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| I got the code for opening all files within a particule folder yesterday
| from William through NG.
|
| When I wrote to William I had the requirement for opening all the Excel
| files within the folder "c:\ccapps\ttlview\tmp\" but while fitting it with
| other parts of the code I find that I need to open all the Excel files one
| step down from "tmp" folder also. That step down folder will be determined
| from today's date, formatted as "dd-mmm-yy".
|
| I modified his code to the following one :-
|
| Sub savingasnewname()
| With Application.FileSearch
| .NewSearch
| p = Format(Now, "dd-mmm-yy")
| .LookIn = ("c:\ccapps\ttlview\tmp\" & p)
| .SearchSubFolders = False
| .FileType = msoFileTypeExcelWorkbooks
| .Execute
| For i = 1 To .FoundFiles.Count
| 'MsgBox "Hi"
| Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
|
| Next i
| End With
|
| End sub
|
| I thought that today being 10-Jun-04 I will get all the excel files within
| folder "c:\ccapps\ttlview\tmp\10-Jun-04" bring opened, but the code wasnt
| executing anything. I introduced a message box statement to know if the
code
| was going inside the "for loop" but it is not.
|
| I understand that the problem is in ".Lookin" statement where Im combining
a
| constant and a variable. Is there any way I may resolve the same.
|
| ( The folder by today's date would always exist because this code is the
| last in a sequence of codes and the previous codes have already created
| folder with name being today's date.)
|
| Regards,
| Hari
| India
|
|

 

[Hari]

HI William,

Thanx a lot.

I tried ur way in the morning and that time it didnt wok.

After ur postn I tried now and surprisingly it worked. ( Will keep u posted
if it doesnt work after some time)

Thanx again for solving my problems one after another.

Regards,
Hari
India