How can i use VBA to import favorites from either IE or the windows
explorer, making sure to also copy the hierarchy of folders within which the
shortcuts reside? In the end, I am looking for a VBA procedure that will
create a list in Excel of this hierarchy.

I have no idea how to do this, however, and the documentation on controlling
IE via VBA seems scanty. Thanks!

Why re-invent the wheel?

Use Tushar Mehta's Excel Add-in.

http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing.

Download the ZIP file and un-zip to your Office\Library folder.

Gord Dibben Excel MVP

On Fri, 2 Jan 2004 16:25:08 -0500, "R Avery" wrote:

How can i use VBA to import favorites from either IE or the windows
explorer, making sure to also copy the hierarchy of folders within which the
shortcuts reside? In the end, I am looking for a VBA procedure that will
create a list in Excel of this hierarchy.

I have no idea how to do this, however, and the documentation on controlling
IE via VBA seems scanty. Thanks!

This enables the listing of the contents of a directory, such as
"C:\Documents and Settings\user\Favorites", but does not go into the .lnk
or .url files to retrieve the Target of that shortcut. That is what i need.
Any thoughts on that?




<Gord Dibben wrote in message
...
Why re-invent the wheel?

Use Tushar Mehta's Excel Add-in.

http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing.

Download the ZIP file and un-zip to your Office\Library folder.

Gord Dibben Excel MVP

On Fri, 2 Jan 2004 16:25:08 -0500, "R Avery" wrote:

How can i use VBA to import favorites from either IE or the windows
explorer, making sure to also copy the hierarchy of folders within which
the
shortcuts reside? In the end, I am looking for a VBA procedure that will
create a list in Excel of this hierarchy.

I have no idea how to do this, however, and the documentation on
controlling
IE via VBA seems scanty. Thanks!

You could use BookMark Wizard from MoonSoftWare to build an *.HTM page which
can be imported to Excel or left as is.

http://www.moonsoftware.com/freeware.asp

Check out CopyURL while there. Good little utility.

My skills with VBA are limited and can give little assistance with that
aspect.

Gord

On Fri, 2 Jan 2004 18:37:03 -0500, "R Avery" wrote:

This enables the listing of the contents of a directory, such as
"C:\Documents and Settings\user\Favorites", but does not go into the .lnk
or .url files to retrieve the Target of that shortcut. That is what i need.
Any thoughts on that?




<Gord Dibben wrote in message
.. .
Why re-invent the wheel?

Use Tushar Mehta's Excel Add-in.

http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing.

Download the ZIP file and un-zip to your Office\Library folder.

Gord Dibben Excel MVP

On Fri, 2 Jan 2004 16:25:08 -0500, "R Avery" wrote:

How can i use VBA to import favorites from either IE or the windows
explorer, making sure to also copy the hierarchy of folders within which
the
shortcuts reside? In the end, I am looking for a VBA procedure that will
create a list in Excel of this hierarchy.

I have no idea how to do this, however, and the documentation on
controlling
IE via VBA seems scanty. Thanks!

Bill Manville has posted code that retrieves all the files in a folder (and its
subfolders):

http://groups.google.com/groups?thre...eeb6%40msn.com

I modified it ever so slightly to just bring back the .URL files. (I think
that's what the extension is for the shortcuts to the web in Favorites. (I
guessed that you only wanted internet favorites??).

Option Explicit
Option Base 1
Dim aFiles() As String
Dim iFile As Long
Sub testme01()

Dim oRow As Long
Dim wks As Worksheet

Dim wShell As Object 'As IWshShell_Class
Dim myLink As Object 'As IWshRuntimeLibrary.WshShortcut

'Set wShell = New IWshShell_Class
Set wShell = CreateObject("WScript.Shell")

Set wks = Worksheets.Add

Call ListFilesInDirectory("C:\windows\favorites\")

For oRow = LBound(aFiles) To UBound(aFiles)
Set myLink = wShell.createshortcut(aFiles(oRow))
wks.Cells(oRow, 1).Value = aFiles(oRow)
'wks.Cells(oRow, 2).Value = myLink.targetpath
'or to make it a hyperlink
wks.Cells(oRow, 2).Formula = "=hyperlink(" & Chr(34) _
& myLink.targetpath & Chr(34) & ")"
Next

End Sub

Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Long, stFile As String

' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' note that Dir returns files as well as directories when vbDirectory
' specified

iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile < Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf GetAttr(stFile) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(iDir)
aDirs(iDir) = stFile
Else
'just return .URL files
' add to global array of files
If LCase(stFile) Like "*.url" Then
iFile = iFile + 1
ReDim Preserve aFiles(iFile)
aFiles(iFile) = stFile
End If
End If
stFile = Directory & Dir()
Loop

' now, for any directories in aDirs call self recursively
If iDir 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub


Change this line to your favorites folder.
Call ListFilesInDirectory("C:\windows\favorites\")




R Avery wrote:

How can i use VBA to import favorites from either IE or the windows
explorer, making sure to also copy the hierarchy of folders within which the
shortcuts reside? In the end, I am looking for a VBA procedure that will
create a list in Excel of this hierarchy.

I have no idea how to do this, however, and the documentation on controlling
IE via VBA seems scanty. Thanks!

--

Dave Peterson