Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • 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 BottomleyCommented:
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 BottomleyCommented:
Apologies ... into a word document not excel!

Chris
0
 
Chris BottomleyCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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