Adding URL to Favourites bar from Excel 2010 1

[JasonEnsor]

Hi Guys,

I am trying to create an Excel document to store a list of URL's that i can then run a macro on to populate them in to my Favourites Bar in Internet Explorer. I have managed to get this to work for placing the the urls on my Desktop and for placing them in my Favourites Folder, however if i try to access the favourites bar it throws up an error.

Run-time Error '-2147467259(80004005)' Unable to save shortcut "C:\Users\Test\Favourites\Favourites Bar\Google.url"

Any Ideas?

Option Explicit

Sub CreateShortcuts()
    Dim oWSH As Object
    Dim oShortcut As Object
    Dim sPathToDesktop
    Dim sPathToFavourites
    Dim sPathToFavouritesBar
    Dim currentLastRow As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim shortcut As Range
    
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Favourites")
    Set oWSH = CreateObject("WScript.Shell")
    
    sPathToDesktop = oWSH.SpecialFolders("Desktop")
    sPathToFavourites = oWSH.SpecialFolders("Favorites")
    
    currentLastRow = LastRow(ws)
    
    For Each shortcut In ws.Range("A2:A" & currentLastRow)
        
        Dim shortcutTitle As String
        shortcutTitle = ws.Cells(shortcut.Row, 1)
        
        Set oShortcut = oWSH.CreateShortCut(sPathToFavourites & "\Favourites Bar\" & shortcutTitle & ".url")
        
        With oShortcut
            .TargetPath = ws.Range("B" & shortcut.Row)
            .Save
        End With
    Next shortcut
    
    Set oWSH = Nothing
End Sub

Function LastRow(sh As Worksheet)

    On Error Resume Next
    
    LastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
                            LookAt:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0

End Function

Many Thanks

J.

Regards

J.

 

[combo]

Save" does not create folders, do you have it ("\Favourites Bar\" in favourites)?

combo

 

[JasonEnsor]

Hi Combo,

I assumed the folder Favourites Bar was there as if i go in to the Favourites folder it is in the directory. It would seem however if i open the Favourites Bar Folder up and check the folder path. it points to Links not Favourites bar.

So i now have it all sorted. Thank you for your help

J.

Regards

J.

 

[SkipVought]

In my town, the "Favorites Bar" includes Billy Bobs and White Elephant Saloon.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

In my town, the "Favorites Bar" includes Billy Bobs and White Elephant Saloon.

This may be a reason of problems with saving too.

combo

 

[SkipVought]

Yea, patrons are sometimes hard to save.

...oh yes, I forgot another bar in the next town; Skippy's Mistake. Mustuv been a Freudian fergot.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}