how do I reference latest workbook in series?

[SidBord]

It sounds like you could make use of a "static" variable.
This is kinda clutzy, but it should work. In the module
that contains all macros that might need to reference the
last workbook, enter "Dim LastWkBkNo As Integer" at the TOP
of the module BEFORE the first Sub statement. That should
make the variable available to all procedures. You can
store whatever you want to in it, but it sounds like the
value of "i" might be a good choice. Or make it a string
variable and store the full name of the workbook.

 

[Dave Peterson]

I think I liked your first routine better--just in case some file was deleted
(via window explorer???).

But if you could trust the date/time on the file, you could use .filesearch to
pick off the newest file:


With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\my documents\excel"
.Filename = "analysis *.xls"
.SearchSubFolders = False
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
MsgBox .FoundFiles(1) & vbLf & FileDateTime(.FoundFiles(1))
End If
End With

And then split it up and extract the last number used.

 

[Dave Peterson]

ps. I use the date/time in the workbook name. I get the same backup, and it's
pretty much unique just by having a few ticks go by on the clock.

stFileName = FolderName & "Analysis _" & format(now,"yyyymmdd_hhmmss") & ".xls"

it makes life easier when cleaning up, too.
(Named _20010612_132254.xls???? It's old, just dump it!)