none
excel 2013 -- generate list of pinned workbooks RRS feed

  • Question

  • Our IT department replaced my computer and when I went into excel on the new box all the workbooks I had pinned on the old machine were gone.

    How can I generate a list of my pinned workbooks before moving to a new box?

    Thanks

    Christian Bahnsen

    Bonus question: how can I take that list and programmatically populate them as pinned workbooks in the new instance of excel?

    Thursday, July 25, 2019 11:11 AM

Answers

  • To:  Christian
    Re:  How can I generate a list of my pinned workbooks before moving to a new box?

    The code works on xl2010.  It adds the list to Column A on the first worksheet...
    '---
    Sub RecentFilesTest()
      Dim lngTotal As Long
      Dim Ndex As Long
      Dim FileListing() As String
     
      lngTotal = Excel.Application.RecentFiles.Count
      ReDim FileListing(1 To lngTotal, 1 To 1)
      For Ndex = 1 To lngTotal
       FileListing(Ndex, 1) = Application.RecentFiles.Item(Ndex).Path
      Next
      With Worksheets(1) 'first worksheet
       .Range(.Cells(1, 1), .Cells(lngTotal, 1)).Value = FileListing()
      End With
    End Sub
    '---

    Re:  populate new excel instance
    The RecentFiles object has an Add method...
      Application.RecentFiles.Add(item)

    Bonus points if you do it yourself.
    '---


    Free Excel workbooks and Add-ins at MediaFire...


    Thursday, July 25, 2019 1:56 PM

All replies

  • To:  Christian
    Re:  How can I generate a list of my pinned workbooks before moving to a new box?

    The code works on xl2010.  It adds the list to Column A on the first worksheet...
    '---
    Sub RecentFilesTest()
      Dim lngTotal As Long
      Dim Ndex As Long
      Dim FileListing() As String
     
      lngTotal = Excel.Application.RecentFiles.Count
      ReDim FileListing(1 To lngTotal, 1 To 1)
      For Ndex = 1 To lngTotal
       FileListing(Ndex, 1) = Application.RecentFiles.Item(Ndex).Path
      Next
      With Worksheets(1) 'first worksheet
       .Range(.Cells(1, 1), .Cells(lngTotal, 1)).Value = FileListing()
      End With
    End Sub
    '---

    Re:  populate new excel instance
    The RecentFiles object has an Add method...
      Application.RecentFiles.Add(item)

    Bonus points if you do it yourself.
    '---


    Free Excel workbooks and Add-ins at MediaFire...


    Thursday, July 25, 2019 1:56 PM
  • Thanks and well done!
    Thursday, July 25, 2019 2:32 PM
  • We just upgraded to Office 2016 and this code saved me a lot of headache.

    Here's what I used to restore the list of files from the first worksheet:

    Sub pinnedWorkbooksRestore()
    '
    ' pinnedWorkbooksRestore Macro
    ' this restores a list of workbooks stored on sheet 1 of my macros workbook to the recently used list 
    ' unfortunately you have to re-pin them manually
    
        Range("A1").Select
        
        While ActiveCell.Value <> ""
            
            Application.RecentFiles.Add (ActiveCell.Value)
            
            ActiveCell.Offset(1, 0).Select
           
        Wend
    
    End Sub
    

    Friday, March 13, 2020 12:07 PM