Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Name of Workbook on Close
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. -- Steph |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Name of Workbook on Close
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. -- Gary''s Student - gsnu200780 "Steph" wrote: 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. -- Steph |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Name of Workbook on Close
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" wrote: 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. -- Steph |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Name of Workbook on Close
Thank you - this helps very much.
-- Steph "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200780 "Steph" wrote: 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. -- Steph |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Record Name of Workbook on Close
Thank you - this helps very much.
-- Steph "Mike H" wrote: 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" wrote: 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. -- Steph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide all Sheets in Workbook then Close Workbook | Excel Programming | |||
Open New Workbook / Save and Close Current Workbook | Excel Programming | |||
Closing a workbook from a macro doesn't close the workbook | Excel Programming | |||
Help on Workbook close and workbook save events | Excel Programming | |||
Close a the current workbook and load another specified workbook | Excel Programming |