Working with FileSystem

Method 1: Using Microsoft Scripting Run time 

  Option Explicit
Const myFolderPath = "C:\Documents and Settings\kollol\My Documents"

Private Sub CommandButton1_Click()

Dim myFol As String

Dim startCell As Range
Set startCell = Application.InputBox(Prompt:="Select the first cell where file name will be inserted", Type:=8)
startCell.Select

myFol = InputBox("Type Folder Name")

Dim myFilesystem As Scripting.FileSystemObject
Set myFilesystem = New Scripting.FileSystemObject

Dim myFolder As Scripting.Folder
        If myFilesystem.FolderExists(myFolderPath & "\" & myFol) Then
            Set myFolder = myFilesystem.GetFolder(myFolderPath & "\" & myFol)
        End If

Dim foundFile As Scripting.File
Dim eachFile As Object

For Each eachFile In myFolder.Files
   Set foundFile = eachFile
   ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:=foundFile.Path, TextToDisplay:=Right(foundFile.Name, 10)
 
   ActiveCell.Offset(1, 0).Select
 
Next eachFile


End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Method :2 Createobject("  ")

The only difference are:

1. 

Dim myFilesystem As Object
Set myFilesystem = CreateObject("Scripting.FileSystemObject")

2. All of the other filesystem objects not to be declared as Object NOT scripting.object

example:

instead of Dim myFolder As Scripting.Folder , now it would be:

Dim myFolder As Object

(Dis)Advantages:

Using Scripting.Runtime we will be able to use intellisense. The problem with this method is- it will return error if the user do not have Microsoft Scripting Runtime turned on.

Using Create object is version neutral- can be used in any version of Excel. The problem is- we can not useintellisense.

A good Idea:

The good Approach is to write the program using method-1  and then just make the changes in method-2 to make it version neutral.

-Syed Uddin 2014