VBA Open File with Random Number in between

[Jim_Doug]

Hi all,

I `m having a problem. And I cannot find a solution for it.

I want to open an excel file with vba.
The location is always the same.

The file I want to open is built by , filename, date, random code, “.xlsx”.
I want to open the file with the latest date.

The problem is the random code.
I don`t know how to access and open the file with VBA, because I can`t handle the random code.
Does anybody have any ideas?


Location:
\\AMSTbiap05\E1SharedFolder\Sales\

Example filename at location.

Excess v5-en-us_2012-04-12T225445458Z.xlsx

Excess v5-en-us_2012-09-13T085902916Z.xlsx

Excess v5-en-us_2012-09-17T034355309Z.xlsx ….. ….. ….. …..

<tbody>
</tbody>

 

[Special-K99]

Write some VBA to write a directory listing into a file, read that file and open the specific file you need.
Of course in your example there are two files beginning with the same date so that may present a further problem.

 

[Jim_Doug]

Tkx Special-K99 for the advice. You helpt me in the right direction!

I managed to built the list in Excel with vba.
Trough Excel folumula I filter the right Link to the file.
And open the Link with vba


Public Sub List_Files_In_Directory()
Range("A5:A2000").ClearContents
Dim List_Files_In_Directory(10000, 1)
Dim One_File_List As String
Dim Number_Of_Files_In_Directory As Long
'One_File_List = Dir$("P:" + "\*.*")
One_File_List = Dir$("\\AMSTbiap05\E1SharedFolder\Sales\Public" + "\*.*")
Do While One_File_List <> ""
List_Files_In_Directory(Number_Of_Files_In_Directory, 0) = One_File_List
One_File_List = Dir$
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Loop
Number_Of_Files_In_Directory = 0
While List_Files_In_Directory(Number_Of_Files_In_Directory, 0) <> tom
Range("A5").Offset(Number_Of_Files_In_Directory, 0).Value = List_Files_In_Directory(Number_Of_Files_In_Directory, 0)
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Wend
End Sub

 

[Jim_Doug]

Tkx Special-K99 for the advice. You helpt me in the right direction!

I managed to built the list in Excel with <acronym title="visual basic for applications">vba</acronym>.
Trough Excel folumula I filter the right Link to the file.
And open the Link with <acronym title="visual basic for applications">vba</acronym>


Public Sub List_Files_In_Directory()
Range("A5:A2000").ClearContents
Dim List_Files_In_Directory(10000, 1)
Dim One_File_List As String
Dim Number_Of_Files_In_Directory As Long
'One_File_List = Dir$("P:" + "\*.*")
One_File_List = Dir$("\\AMSTbiap05\E1SharedFolder\Sales\Public" + "\*.*")
Do While One_File_List <> ""
List_Files_In_Directory(Number_Of_Files_In_Directory, 0) = One_File_List
One_File_List = Dir$
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Loop
Number_Of_Files_In_Directory = 0
While List_Files_In_Directory(Number_Of_Files_In_Directory, 0) <> tom
Range("A5").Offset(Number_Of_Files_In_Directory, 0).Value = List_Files_In_Directory(Number_Of_Files_In_Directory, 0)
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Wend
End Sub