recent files in a list box

[yinkajewole]

using a listbox, how do i let it display up to 60 recently opened files.

 

[yinkajewole]

@yinkajewole - perhaps you did not see my solution in post#4

@My Aswer Is This confirmed that the solution posted works well
Q1 Did you test it?


Q2 Why would this be helpful? Are you wanting a longer list than Excel maintains?

yes, the code worked. but i want a longer list than excel maintains, i would also need a command button to open any of them

 

[Yongle]

I want a longer list than Excel maintains

- I have a few ideas that I will test tomorrow

 

[yinkajewole]

@yinkajewole - perhaps you did not see my solution in post#4

@My Aswer Is This confirmed that the solution posted works well
Q1 Did you test it?


Q2 Why would this be helpful? Are you wanting a longer list than Excel maintains?

- I have a few ideas that I will test tomorrow

ok, no problem

 

[Yongle]

Use a NEW workbook
- VBA below maintains a list of "recent" files in column A of the active sheet
- expect the list to contain 50 files the first time
- run again after working on other files
- list increases to a maximum of 60 "recent" files (as requested in post#1)

Sub Latest_60_Files()
    Dim r As Long
    'update list in sheet
    Rows("1:50").Insert
    For r = 1 To 50
        Range("A" & r) = Application.RecentFiles(r).Path
    Next
    'remove duplicates, empty cells & old entries
    Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    On Error Resume Next
    Range("A1").Resize(120).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Range("A1").Offset(60).Resize(60).ClearContents
    'save workbook
    ThisWorkbook.Save
End Sub

The above code should provide the correct values to populate your listbox

Some sample ListBox code which may help you:

Remove prior entries in ListBox with

     ListBox1.Clear

To add items to a ListBox (as previously demonsted in post#4)

    Dim x As Integer
    For r = 1 To 60
        ListBox1.AddItem Range("A" & r ).Value
    Next r

To open a workbook from the ListBox (perhaps using Private Sub ListBox1_Change)

    Workbooks.Open (ListBox1.Value)

Remember to add controls to prevent VBA failing
"Recent" file list in Excel is NOT updated when a file is deleted or moved
So perhaps something along these lines

   On Error Resume Next
   Workbooks.Open (ListBox1.Value)
   If Err.Number > 0 Then MsgBox "File not found"
   On Error GotTo 0

 

[yinkajewole]

Use a NEW workbook
- VBA below maintains a list of "recent" files in column A of the active sheet
- expect the list to contain 50 files the first time
- run again after working on other files
- list increases to a maximum of 60 "recent" files (as requested in post#1)

Sub Latest_60_Files()
    Dim r As Long
    'update list in sheet
    Rows("1:50").Insert
    For r = 1 To 50
        Range("A" & r) = Application.RecentFiles(r).Path
    Next
    'remove duplicates, empty cells & old entries
    Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    On Error Resume Next
    Range("A1").Resize(120).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Range("A1").Offset(60).Resize(60).ClearContents
    'save workbook
    ThisWorkbook.Save
End Sub

The above code should provide the correct values to populate your listbox

Some sample ListBox code which may help you:

Remove prior entries in ListBox with

     ListBox1.Clear

To add items to a ListBox (as previously demonsted in post#4)

    Dim x As Integer
    For r = 1 To 60
        ListBox1.AddItem Range("A" & r ).Value
    Next r

To open a workbook from the ListBox (perhaps using Private Sub ListBox1_Change)

    Workbooks.Open (ListBox1.Value)

Remember to add controls to prevent VBA failing
"Recent" file list in Excel is NOT updated when a file is deleted or moved
So perhaps something along these lines

   On Error Resume Next
   Workbooks.Open (ListBox1.Value)
   If Err.Number > 0 Then MsgBox "File not found"
   On Error GotTo 0

does this means it will save the recent files in every workbook I open?

 

[Yongle]

The list should be maintained in one workbook only (either the workbook containing the macros or a different workbook)

To access listbox whenever Excel is open there are various options ...
1 open workbook in usual way and run the macro automatically (and close the file after name selected in listbox)
2 consider using your Personal macro workbook - see https://bettersolutions.com/excel/macros/personal-xlsb.htm
3 consider creating an Excel Add-in - see https://trumpexcel.com/excel-add-in/

 

[My Aswer Is This]

I have been monitoring this thread from the beginning.
You original post did not say what your ultimate goal is.

Are you wanting to be able to select a file name from a listbox and then have a script open that file.

And are you wanting this to happen on any workbook you may open.

And if that is the case I would suggest putting a listbox or a Combobox in a UserForm.

Put the UserForm in your Personal folder.

Use a Keyboard shortcut to open the UserForm.

When the UserForm is opened last 60 files or what ever number are loaded into the UserForm ListBox

When you click on the file name in the listbox that file will be opened.

I would suggest using a Combobox. If you want a larger number of file named loaded it would be easier for you to navigate a Combobox then a very long Listbox


I'm just commenting here this may not be what you want.

But I do believe telling us the ultimate goal is easier then asking for one simple task like you mentioned in post one and then adding more to your request as answers are provided.

And you said you wanted more then 60 but did not say how many. Will it be 100 or 350

 

[Yongle]

You did not say in post#15 whether you had tested the macro Latest_60_Files
- I therefore assume that you tested it and that it correctly listed the files that you expected

Below is a method (to finish the job) allowing you to open any one of 60 recent files whenever Excel is open

The method works if you follow these instructions very carefully and in EXACTLY the same sequence
- please use my file & procedure names (they are used in the Add-In and everything must match up)
- if anything does not work correctly solve that problem FIRST before moving to next step!
- if you need help please ask
- I am very busy until Tuesday so do not expect immediate response

CLOSE Excel completely before doing anything
- do not keep any other workbooks open

Create the workbook which maintains list of recent files

1. Open a NEW workbook, add the VBA below, save that workbook (as macro-enabled) with name "RecentFileList"

Place in ThisWorkbook module (will not work in a standard moule)

Private Sub Workbook_Open()
    Application.EnableEvents = False
    Sheet1.Range("B1").Select
    Application.EnableEvents = True
    Call Sheet1.Latest_60_Files
    MsgBox "Click on empty cell to close workbook" & vbcr & "or file name to open file"
End Sub

Place in SHEET module for Sheet1 (will not work in a standard moule)

Sub Latest_60_Files()
    Dim r As Long
    'update list in sheet
    Rows("1:50").Insert
    For r = 1 To 50
        Range("A" & r) = Application.RecentFiles(r).path
    Next
    'remove duplicates, empty cells & old entries
    Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    On Error Resume Next
    Range("A1").Resize(120).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Range("A1").Offset(60).Resize(60).ClearContents
    'save workbook
    ThisWorkbook.Save
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    On Error Resume Next
    Workbooks.Open (Target)
    If Err.Number > 0 Then MsgBox "File not found"
    On Error GoTo 0
    ThisWorkbook.Close False
End Sub

2. Close the workbook and re-open to ensure it works as expected

Create Excel Addin

1. Open a new workbook and add procedure below in a STANDARD module
- amend the PATH to wherever you saved RecentFileList.xlsm
Sub Get_Recent_Files()
Workbooks.Open Filename:="C:\Folder\SubFolders\RecentFileList.xlsm"
End Sub

2. Run Get_Recent_Files to ensure RecentFileList.xlsm opens

3. Click on any cell to close RecentFileList.xlsm BEFORE step 3

4. Save workbook as file type Excel Add-in (scroll down until you find it)
- the workbook is saved with extension .xlam
- the SaveTo folder changes automatically to the default folder for add-ins (do not save it anywhere else!)
- name the add-in "RecentFiles"

5. Close the file

Install the Addin

1, Select Excel Developer Tab \ Excel Add-Ins \ (below Add-ins Available) check the box next to RecentFiles \ click OK

2. Right-Click on ribbon and select Customise quick Access Toolbar

3. Under Choose Commands From select Macros (brings up list of macros available)

4. Click on "Get_Recent_Files" \ click ADD \ click MODIFY \ choose any ICON \ click OK

5. Test the Addin by clicking on the icon in the quick access toolbar

@yinkajewole

If you have any questions for My Aswer Is This begin with @My Aswer Is This

If you have any questions for me begin with @Yongle

Please do not "quote" the whole of this post if replying

 

[yinkajewole]

@Yongle many thanks for all of these. i'll get back to you all later

 

[Yongle]

I have another solution for you which you may prefer
- it involves putting the Recent Files workbook into your XLSTART folder
- it is very similar to solution in post#18 (but slightly simpler)
- I will post it after you have fully tested what is in post#18