Record Name of Workbook on Close

[Steph]

I am trying to get at a procedure that extends the recent files list for
Excel 2003. Is there a way to record the names of all workbooks onto a
separate Excel file (like maybe Personal.xls) when they are closed? Idealy,
I want to track the name of any workbook I close because the recent files
list only allows a history of up to nine - I have a lot of workbooks that I
open and close and am forgetting where they are on the network.

 

[Gary''s Student]

Lets assume that whenever you are working you have a workbook called log.xls
open. Put the following workbook event code in all your other workbooks:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set sl = Workbooks("log.xls").Sheets("logsheet")
s = ThisWorkbook.Name
n = sl.Cells(Rows.Count, 1).End(xlUp).Row + 1
sl.Cells(n, 1) = s
End Sub

Before closing a workbook, its name will be recorded in the log workbook.

 

[Mike H]

Hi,

You could put this in module in personal.xls and create a button to run it
in each workbook or call it from the close event

Sub recent()
Dim MyFile As String
Dim MyText As String
Dim FileNum As Integer
MyFile = "c:\recent_files.txt"
MyText = ActiveWorkbook.FullName
FileNum = FreeFile
Open MyFile For Append As FileNum
Write #FileNum, MyText
Close #FileNum
End Sub

the full path name is written to a text file in the root of C but that could
be anywhere.

Mike

 

[Steph]

Thank you - this helps very much.

--
Steph

Lets assume that whenever you are working you have a workbook called log.xls
open. Put the following workbook event code in all your other workbooks:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set sl = Workbooks("log.xls").Sheets("logsheet")
s = ThisWorkbook.Name
n = sl.Cells(Rows.Count, 1).End(xlUp).Row + 1
sl.Cells(n, 1) = s
End Sub

Before closing a workbook, its name will be recorded in the log workbook.

 

[Steph]

Thank you - this helps very much.

--
Steph

Hi,

You could put this in module in personal.xls and create a button to run it
in each workbook or call it from the close event

Sub recent()
Dim MyFile As String
Dim MyText As String
Dim FileNum As Integer
MyFile = "c:\recent_files.txt"
MyText = ActiveWorkbook.FullName
FileNum = FreeFile
Open MyFile For Append As FileNum
Write #FileNum, MyText
Close #FileNum
End Sub

the full path name is written to a text file in the root of C but that could
be anywhere.

Mike