Renaming Files using Wildcards with VBA Code

'I would like to use a wildcard'  
...to indicate what?

IF LEFT(filename,6)="ALL_RC" then

If I may expend - I think what Pete is asking you is:
In what way do you want to use a wildcard?  To return what file types/names?
And by what criteria do you want to rename the file?  To the first 6 characters? To the second occurance of an underscore?  You need to provide hard and fast axioms to create routines in an application.

(Alas the VBA routine DoWhateverYouThinkMakesSense isn't implemented yet :-)

What do the second, third, ... , etc. files that need to be renamed look like?  

Would like to use the wildcard as follows:

   original file:  ALL_RC_20070226064133_Query 1 with ELGDISHP.txt

  original file with wildcard:  ALL_RC*..txt

  and rename as follows:  ALL_RC.txt

The second and third files are the same with the exception of the beginning part of the filename (ALL_RC_).  The 2007.... represents the date and time (yyyymmddhhmmss), so this part of the filename will be different also, depending on the date/time file was processed.

Thanks,
gdunn59

You may need to clarify exactly what it is that you are wanting to accomplish.  There are many ways to interact with files using file scripting objects, but I would need to clearly understand the ultimate goal in order to best provide assistance.

Given you want to replace the filename with the first six letter of the filename, is there some common substring in the part you are deleting that you can use for a wild card, ie may the end of the name - ELGDISHP?  Perhaps you could post 5 or 10 names and show us what you want them reduced to?

GRayL,

I already posted the examples in my last post. I show what the original filename is, how I want to use the file with a wildcard, and how I want the file to be renamed.

Why does this not work?

IF LEFT(filename,6)="ALL_RC" then

It doesn't seem like you need a wildcard. What seems wierd is how are you going to handle multiple files of the same thing? Or do you just want to truncate everything to the first 6 letters?

then just use filename = left(filename,6)

I just want to use the first few characters.  

How would I go about incorporating this into my docmd statement below:

DoCmd.TransferText acImportDelim, "ALL_RC_Import_Spec", "All_Reason_Code", "S:\Systems Support\Systems Support\Mindi's Daily Report\Daily Reports\Log Source Files\ALL_RC_20070226064133_Query 1 with ELGDISHP.txt", True, "", 20127

thanks,
gdunn59

So then you are attempting to import multiple files right?

Have a look at this:

Dim stFileName as string
Dim stFilePath as string
Dim stNewName as string

stFilePath = "S:\Systems Support\Systems Support\Mindi's Daily Report\Daily Reports\Log Source Files"

dirStart =  "\\MyServer\D$\MyFolder\InBox\"
dirEnd = "\\MyServer\D$\MyFolderArchives"

Set fs = CreateObject("Scripting.FileSystemObject")
Set oFolder = fs.GetFolder(dirStart)
Set oFiles = oFolder.Files

For each oFile in oFiles
      stFileName =  stFilePath & "\" & oFile.name
                 stNewName = left(oFile.name,6)

DoCmd.TransferText acImportDelim, "ALL_RC_Import_Spec", "All_Reason_Code", stFileName, True, "", 20127

If Not fs.FolderExists(dirEnd) then fs.CreateFolder(dirEnd)

If fs.FileExists(dirEnd & "\" & stFileName & "_" & stStamp) then fs.DeleteFile(dirEnd & "\" & stFileName  & "_" & stStamp)

If fs.FileExists(oFile.Path) then
      fs.MoveFile oFile.Path, dirEnd & "\" & stNewName & right(stFileName,4)
End If


Next
set fs = nothing

The answer to your question regarding importing multiple files is "yes", but each file is being imported into a different table.  So there are 3 files to be imported into 3 different tables.

Here are the 3 files:
        FILES                                                                                                  TABLE
   1.  ALL_RC_20070226064133_Query 1 with ELGDISHP.txt                  All_Reason_Code
   2.  RESOLVED_20070226064212_Query 1 with ELGRESHP.txt            Productivity___Resolved
   3.  ACTIVE_20070226064149_Query 1 with ELGDISHP.txt                   Productivity___Active