ACC97: How to Convert Internet Shortcuts to Hyperlinks in a Table

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create a Visual Basic for Applications procedure that reads the hyperlinks in a folder called C:\MyShortcuts and copies them into a new Microsoft Access table called tblHyperlinks, follow these steps:

1. Start Windows Explorer and create a new folder on your hard disk called C:\MyShortcuts.
2. Copy several Internet shortcuts into the folder C:\MyShortcuts. You can copy Internet shortcuts from your Favorites folder. To locate the Favorites folder, on the Tools menu, point to Find, and then click Files Or Folders.
3. Start Microsoft Access and create a new, blank database.
4. Open a new module and type the following procedure:

      Function MakeShortcutTable(strTableName As String, _
               strDirectoryPath As String)
   
            Dim strGrab As String
            Dim strFileName As String
            Dim intFileNum As Integer
            Dim dbs As Database
            Dim Rst As Recordset
            Dim tdf As TableDef
            Dim fld As Field
            Dim nextChar As String
            
   
            ' Return reference to current database.
            Set dbs = CurrentDb
   
            ' Return TableDef object variable that points to new table.
            Set tdf = dbs.CreateTableDef(strTableName)
   
            ' Define new field in table.
            Set fld = tdf.CreateField("Link", dbMemo, 233)
   
            ' Append Field object to Fields collection of TableDef object.
            fld.Attributes = dbHyperlinkField
            tdf.Fields.Append fld
            tdf.Fields.Refresh
   
            ' Append TableDef object to TableDefs collection of database.
            dbs.TableDefs.Append tdf
            dbs.TableDefs.Refresh
            Set Rst = dbs.OpenRecordset(strTableName)
            strFileName = Dir(strDirectoryPath & "*.url")
   
            ' Loop to open each matching file in the current folder.
            Do While Len(strFileName) > 0
        
               intFileNum = FreeFile
               ' Open the file.
               Open strDirectoryPath & strFileName For Input As _
                   #intFileNum
               ' Get first 24 characters.
                strGrab = Input(24, #intFileNum)
               strGrab = ""
   
               ' Loop until end of file.
               Do While Not EOF(intFileNum)
               nextChar = Input(1, #intFileNum)
               If nextChar = "[" Then Exit Do
                 strGrab = strGrab & nextChar
                 
               Loop
   
               Close #intFileNum    ' Close file.
               MsgBox Left(strFileName, Len(strFileName) - 4) & _
                     "#" & Left(strGrab, Len(strGrab) - 2) & "x"
               With Rst
                  .AddNew
                  !link = Left(strFileName, Len(strFileName) - 4) & _
                     "#" & Left(strGrab, Len(strGrab) - 2)
                  .Update
               End With
               
               strFileName = Dir()
               ' Repeat the loop for all matching files
               ' in the current folder.
            Loop
            
         Rst.Close
         End Function
   
   					

5. Press CTRL+G to open the Debug window.
6. Type the following line in the Debug window, and then press ENTER:

   ?MakeShortcutTable("tblHyperlinks","c:\MyShortcuts\")
   						

   Note that after you run the procedure, a new table called tblHyperlinks appears in the database. Its data consists of hyperlinks that correspond to the Internet shortcuts in the folder C:\MyShortcuts.