HELP create VBscript to search for and open file in folder

[jugend]

Hi!
I´m fairly new at VB.

I have an a bit complicated issue that i hope someone can help me solve.

I want to use a script to open the last excel spreadsheet in an increment range as for example Order-1.xls, Order-2.xls, Order-3.xls and so on, in a partly known directory name in an unknown parent directory, based on the partly known directory-name as search criteria.

Eks.
F:\Order\xxxxx\093011141534_zzzzz\Order-3.xls

Folder:     F:\Order\                        is known
Folder:    \xxxxx\                          is unknown
Folder:    \093011141534_zzzzz\      is partly known, and 093011141534_ is the unique search criteria
File:        Order-3.xls                       I want to open the last file in an increment range starting with Order*

Is it possible to solve, or is it to many variables?
If anyone could help, it would make my day! 

 

[mrmovie]

strRootFolder = "F:\Order"
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(strRootFolder) Then
Set objFolder = FSO.GetFolder(strRootFolder)
For Each aSubFolder In objFolder.SubFolders
strTargetFile = ""
'presume you want to parse any and all subfolders?
For Each aFile In aSubFolder.Files
'presume that they are returned in the right order in the .Files collection?
'hmm get the last one?
strTargetFile = aFile.Name
Next
Msgbox strTargetFile & " in " & aSubFolder.Name
'call some sub routine to launch the file?
Next
Set objFolder = Nothing
Else
Msgbox "error cannot find " & strRootFolder
End If

 

[jugend]

Thanx for your reply, mrmovie!

The problem is that within F:\Order\xxxxx\ all the subfolders does contain a file called Order-1.xls, but might also contain Order-2.xls, Order-3.xls or further.

So what i need to do is to open the "last" Order-file in the correct subfolder. In this example, Order-3.xls

I have to find a way to, from the rootfolder (F:\Order\), search for the subfolder using just part of the subfoldername (093011141534_). Then get the full path to this subfolder, and finally open the "last" Order*.xls-file within it.

Hope this clarified some of your questions, and that you find time to look at it again

 

[mrmovie]

for clarity, you are not interested in opening each 'last' file in each of the subfolders? you are interested in finding the one and only last file by comparing all the last files in each of the subfolders?

 

[jugend]

I have a search criteria which is a just a part of the subfoldername. I just want to open the last file in this subfolder. Not i all subfolders.

Lets say:
The searchstring that i have is 093011141534.

I want to use this searchstring to open the last Order-file in a folderstructure that might look something like this:

F:\Order\John\093011141534_test\Order-3.xls

I don't know the \John\ subfolder, I don't know the last part of the searchcriteria subfolder name (_test), and i don't know how many Order-files there is within the subfolder. In this example the Order-3.xls is the last file.

The only thing i know is that there is a subfolder starting with 093011141534 somewhere underneath F:\Order\........\ , and that it contains multiple Order-files.

Thanx again!

 

[mrmovie]

'this should do it? it does rely on the .Files collection being returned in the order in which you expect. I have used this with success before but had to name my files correctly, i.e. 000010, 000011 rather than 10 or 11. if this doesnt work then you can use the Array.SortList .net thing rather than a bubble sort
strSubFolderInStr = "123456"
strRootFolder = "F:\Order"
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(strRootFolder) Then
Set objFolder = FSO.GetFolder(strRootFolder)
For Each aSubFolder In objFolder.SubFolders
If InStr(aSubFolder.Name, strSubFolderInStr) Then
strTargetFile = ""
'presume you want to parse any and all subfolders?
For Each aFile In aSubFolder.Files
'presume that they are returned in the right order in the .Files collection?
'hmm get the last one?
strTargetFile = aFile.Name
Next
Msgbox strTargetFile & " in " & aSubFolder.Name
'call some sub routine to launch the file?
End If
Next
Set objFolder = Nothing
Else
Msgbox "error cannot find " & strRootFolder
End If

 

[Geates]

I like mrmovies code, although it might be missing something.

When you say "the last order", is "last" defined by the creation date or the orders index number (Order-3.xls)?

- set search criteria.
- establish a "last" variable (whether index or date).
- traverse folders and subfolders.
- compare file objects (.path and .name) against search criteria and "last" var.
- once both conditions above are met, return file object.

-Geates

 

[jugend]

I like the code too!
I have tryed to make a workoround to get the missing functionality, but if you can give me further help, I would appreciate it!

The "last" file is the one with the highest indexnr.

 

[Geates]

Come to think of it, I wrote someting similar a few weeks ago for someone else..

To paraphrase mrmovie's recent remark, break this task into smaller tasks.

1. Find all order files in a given directory that match a given criteria.

2. Of those found objects, which one meets the criteria for "last" (most recent)

3. Launch "last" object.

CONST strDir = "C:\temp\"
CONST strCriteria = "Order-"

set objShell = CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")

dim strResults

'1. Find all order files in a given directory that match a given criteria
function searchForOrder(strDir, strCriteria)
   set objFolder = objFSO.GetFolder(strDir)
   for each objSubFolder in objFolder.SubFolders
      strResults = searchForOrder (objSubFolder.Path, strCriteria)
   next
	
   for each objFile in objFolder.Files
      if (inStr(objFile.Name, strCriteria)) then strResults = strResults & objFile.Path & vbNewLine
   next
  searchForOrder = strResults
end function

strOrders = searchForOrder(strDir, strCriteria)
arrOrders = split(strOrders, vbNewLine)

'2. Of those found objects, which one meets the criteria for "last" (most recent)

intLastIndex = 0
for each strOrder in arrOrders
   strFrag = right(strOrder, inStrRev(strOrder, "\"))
   strFrag = right(strFrag, inStr(strFrag, "-"))
   intIndex = left(strFrag, inStr(strFrag, "."))
   if (intIndex > intLast) then
      intLast = intIndex
      strLastOrder = strOrder
   end if
next

'3. Launch "last" object.
objShell.Run strLastOrder

-Geates

 

[mrmovie]

nice ones Geates, i kept thinking this should just be a recursion