How to get a dynamic filename

[CCameron]

Hi All,

I have a file that I want to open programmatically, but the filename is
always changing.

F:\myfileAAA.xls
next revision
F:\myfileAAB.xls
:
:
etc...

Now, the previous file is moved to a different directory, and the filename
setup stays the same, so:

can I do something like Workbooks.Open "F:\myfile???.xls" or something like
that?

CCameron

[David McRitchie]

Cannot understand the question, except that the
directory is changing at least that from the text
not the example.

If the purpose is to create a backup you can place
the backup elsewhere and continue with your file
where it has been all along. Don't know if this would
help, but it sure sounds simpler
http://www.mvps.org/dmcritchie/excel/backup.htm
than trying to locate where a file under a new name
is supposed to be found.

You should be able to record a macro while doing what
you want to do.

Workbooks.Open filename:="F:\myfileAAB.xls"

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"CCameron" <a...@b.ca> wrote in message news:O7s$jTTHCHA.2364@tkmsftngp11...

[CCameron]

Hi David,

No, what you are thinking is not what I want.

Picture this:
I have 1000 excel files.
They are located in upto 1000 folders (some files are in the same folder)
In each of these folders there is another folder called OLD
If I have to modify one of these files, lets say [chadc1.xls]

chad - fileid
c - current revision
1 - number of pages

I do the changes and save the file as chadd1.xls [revision went from c ->
d]
the old file [chadc1.xls] is moved to the OLD folder and the current
revision stays in the same folder.

Now I want to printout only 100 excel files. I know the fileid's of all
these files, I don't know the current rev, I do know the number of pages
[always 1] & I know the folder that the current revision is is.

Note: only the current revision is in the folder

So, I want a program that will open the all the files with the current
revision, but I don't know the current revision character. That is why I
want a way that will open a like
Workbooks.Open filename:="F:\chad?1.xls"
I don't care what the revision is, just printout the file that is LIKE
chad?1.xls

"David McRitchie" <dmcri...@msn.com> wrote in message
news:enVtw$THCHA.2512@tkmsftngp13...

[Dave Peterson]

Maybe this'll get you started:

Option Explicit
Sub testme2()

Dim fs As FileSearch
Dim FilePath As String
Dim UserFileName As String
Dim I As Long

FilePath = "C:\my documents\excel"
UserFileName = "chad?1.xls"

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = FilePath
.SearchSubFolders = True
.Filename = UserFileName
.MatchTextExactly = False
If .Execute > 0 Then
For I = 1 To .FoundFiles.Count
If LCase(Dir(.FoundFiles(I))) Like LCase(UserFileName) Then
MsgBox .FoundFiles(I)
End If
Next I
End If
End With
End Sub

--

Dave Peterson
ec3...@msn.com