• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

url list from favourites folder

Dear experts,

Is it possible to have a macro whcih will go the favourites folder in the c drive and then extract the url details and copy it into a word document (one below the other).

In the macro i will mention the favourites folder location and also keep the word document open.

Kindly help.

thank you

0
Excellearner
Asked:
Excellearner
  • 6
  • 3
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try the following which is a simplification of:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/export-all-ie-favorites-to-spreadsheet/111d4122-a7a2-497a-9a9b-f45e123f2d89

Chris
Option Explicit

Sub ListFavs()
    Dim FSO As Object
    Dim FF As Object
    Dim R As Range
    Dim F As Object
    Dim UserName As String
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set R = Sheets(1).Range("A1")
    UserName = Environ("username")
    Set FF = FSO.GetFolder("C:\Documents And Settings\" & UserName & "\Favorites")
    DoOneFolder FSO, FF, R
End Sub
Sub DoOneFolder(FSO As Object, WhatFolder As Object, R As Range)
    Dim F As Object
    Dim FName As String
    Dim FNum As Integer
    Dim SubFolder As Object
    Dim S As String
    
    For Each F In WhatFolder.Files
        R(1, 1) = F.Name
        FNum = FreeFile
        Open F.Path For Input Access Read As #FNum
        Do Until EOF(FNum)
            Line Input #FNum, S
            If StrComp(Left(S, 3), "URL", vbTextCompare) = 0 Then
                R(1, 2).Value = Mid(S, 5)
                Exit Do
            End If
        Loop
        Close #FNum
        Set R = R(2, 1)
    Next F
    For Each SubFolder In WhatFolder.SubFolders
        DoOneFolder FSO, SubFolder, R
    Next SubFolder
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Apologies ... into a word document not excel!

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The following adapts it into a table in a word document.

The code should be placed in the document which is to store the data ... and will refresh the document every time it is run.

Chris
Sub ListFavs()
    Dim FSO As Object
    Dim FF As Object
    Dim F As Object
    Dim UserName As String
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    ThisDocument.Range.Delete
    ThisDocument.Tables.Add ThisDocument.Range(0), 1, 2
    ThisDocument.Tables(1).Cell(1, 1).Range.Text = "Friendly Name"
    ThisDocument.Tables(1).Cell(1, 2).Range.Text = "URL"
    UserName = Environ("username")
    Set FF = FSO.GetFolder("C:\Documents And Settings\" & UserName & "\Favorites")
    DoOneFolder FSO, FF
End Sub
Sub DoOneFolder(FSO As Object, WhatFolder As Object)
    Dim F As Object
    Dim FName As String
    Dim FNum As Integer
    Dim SubFolder As Object
    Dim S As String
    
    For Each F In WhatFolder.Files
        With ThisDocument.Tables(1)
            .Rows.Add
            .Cell(ThisDocument.Tables(1).Rows.Count, 1).Range.Text = F.Name
            FNum = FreeFile
            Open F.Path For Input Access Read As #FNum
            Do Until EOF(FNum)
                Line Input #FNum, S
                If StrComp(Left(S, 3), "URL", vbTextCompare) = 0 Then
                    .Cell(ThisDocument.Tables(1).Rows.Count, 2).Range.Text = Mid(S, 5)
                    Exit Do
                End If
            Loop
            Close #FNum
        End With
    Next F
    For Each SubFolder In WhatFolder.SubFolders
        DoOneFolder FSO, SubFolder
    Next SubFolder
End Sub

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ExcellearnerAuthor Commented:
Chris,

Sorry for not being able to get back any earlier.

I had gone on a long unexpected holiday. Apologies once again.

I have checked the vba it ran correct the first time i loaded the vba ontoa word document. Then savved the document

Then i reopened the file and tried to run it once again and i got an error which i have attached kindly help

Thank you,
Error-re-running-the-vba.doc
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
It's like it says macros are disabled .... the first time presumably you were running it in the same outlook session as the creation activity.  After a start though the outlook rules kick in:

Enabling macros variies with application version but as a starter:

Check Security as appropriate:
------------------------------

In the application select Tools | Macro | Security
=========================
2003 and earlier
-------------------------
      : Select Medium
      : Select OK
=========================

==================================================================================
2007
----------------------------------------------------------------------------------
      : Outlook Application - Warnings for all Macros
----------------------------------------------------------------------------------
      : All Other Applications - Enable a trusted location and inhibit macros otherwise so do both!
      : Disable Macros
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            MAcro Settings
            Disable All MAcros with warnings
      : Enable Trusted Locations
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            Trusted Locations
            Add a preferred location
            ENSURE YOUR FILE IS IN A TRUSTED LOCATION
            Re-open to ensure it is recognised if the above has been changed
            
      : Select OK
==================================================================================
0
 
ExcellearnerAuthor Commented:
Chris,

You got back very fast.

One another thing i wanted to ask you to add was to in the url (underlining, hyperlink) to be enable so that if point the cursor, then page can open,

Is this feasible.

Kindly help.

thank you
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Small change overall, but complete code set supplied for simplicity ... assuming I understand correctly:

Chris
Sub ListFavs()
    Dim FSO As Object
    Dim FF As Object
    Dim F As Object
    Dim UserName As String
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    ThisDocument.Range.Delete
    ThisDocument.Tables.Add ThisDocument.Range(0), 1, 2
    ThisDocument.Tables(1).Cell(1, 1).Range.Text = "Friendly Name"
    ThisDocument.Tables(1).Cell(1, 2).Range.Text = "URL"
    UserName = Environ("username")
    Set FF = FSO.GetFolder("C:\Documents And Settings\" & UserName & "\Favorites")
    DoOneFolder FSO, FF
End Sub
Sub DoOneFolder(FSO As Object, WhatFolder As Object)
    Dim F As Object
    Dim FName As String
    Dim FNum As Integer
    Dim SubFolder As Object
    Dim S As String
    
    For Each F In WhatFolder.Files
        With ThisDocument.Tables(1)
            .Rows.Add
            .Cell(ThisDocument.Tables(1).Rows.Count, 1).Range.Text = F.Name
            FNum = FreeFile
            Open F.Path For Input Access Read As #FNum
            Do Until EOF(FNum)
                Line Input #FNum, S
                If StrComp(Left(S, 3), "URL", vbTextCompare) = 0 Then
'                    .Cell(ThisDocument.Tables(1).Rows.Count, 2).Range.Text = Mid(S, 5)
                    ActiveDocument.Hyperlinks.Add Anchor:=.Cell(ThisDocument.Tables(1).Rows.Count, 2).Range, Address:= _
                            Mid(S, 5), SubAddress:="", ScreenTip:="", TextToDisplay:=Mid(S, 5)
                    Exit Do
                End If
            Loop
            Close #FNum
        End With
    Next F
    For Each SubFolder In WhatFolder.SubFolders
        DoOneFolder FSO, SubFolder
    Next SubFolder
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Excellearner

If that is it any chance you can close the question off this time rather than it be left for another two months?

Chris
0
 
ExcellearnerAuthor Commented:
Chris, thanks much for everything, have a nice weekend.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now