Quote:
|
Originally Posted by Tatooo
I am writing a sub that would access a series of fodlers in the network and open an excel file from each folder. The path for each folder can be recreated, but the name for each file could not since the name contains a date, which is different for each file. Is there a way that by knowing the network path I can read the name of the excel file located in such folder ahead of time so that I can then insert it in the code and open the file.
Here is the code that I am currently using and that is not working:
SN = InputBox("Enter the SN of the starting animal:")
FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
Filename = "ANIMAL" & SN & "_READINGS_" & "YYMMDD" & ".xls"
Workbooks.Open Filename:=FolderName & Filename
Let me point out that if I enter the correct name for the file the code above does open the file. For instance, if I replace the code above for the code below it does open the file "ANIMALSN_READINGS_082509.xls":
SN = InputBox("Enter the SN of the starting animal:")
FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
Filename = "ANIMAL" & SN & "_READINGS_" & "082509" & ".xls"
Workbooks.Open Filename:=FolderName & Filename
Any help would be greatly appreciated.
Regards, Tatooo.
|
Hi
A bit difficult to know precisely what you are trying to do with the date (I think you example should read 080925??) but this routine might help.
Code:
Option Explicit
Sub OpenFiles()
Dim SN As String
Dim FolderName As String
Dim Filename As String
SN = InputBox("Enter the SN of the starting animal:")
If SN = "" Then Exit Sub
FolderName = "C:\Test\ANIMALS\"
Filename = "ANIMAL" & SN & "_READINGS_??????.xls"
Filename = Dir(FolderName & Filename)
Do Until Filename = ""
Workbooks.Open Filename:=FolderName & Filename
Filename = Dir
Loop
End Sub
This routine opens all files in the specified directory with the given name with 6 numbers at the specified date position (see wildcards charaters in help).
For more info on the Dir() function try Excel Help (it is always better to do it yourself, you learn much more that way!!)
HTH
MTB
dBforums
>
Opening an Excel file from network
09-28-09, 12:26
Linear Mode
