Error in loop

Hello everyone,
I have a function that returns the file properties(such as filesize, creation date, modification date) of a file in column A of a excel spread sheet, what I need is to make this function loop trough all the path in column A; I tried to create a loop with some help but right now the loop is working but is not giving me back the values, the function seems to straight away jump in the CASE else part, does anyone knows how to make work properly? spread sheet screen shot
Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Select Case UCase(Trim(myFile))
        Case "CREATED"
            GetFileProperty = oFS.GetFile(myFile).DateCreated
        Case "MODIFIED"
            GetFileProperty = oFS.GetFile(myFile).DateLastModified
        Case "ACCESSED"
            GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
        Case "SIZE"
            GetFileProperty = oFS.GetFile(myFile).Size
        Case Else
            GetFileProperty = "txt"
    End Select
End Function

Public Sub SperiamoLoop()
    Dim MyCell As Variant, Rng As Range
    Dim myFile As String, myType As String
    Set Rng = Sheets("Sheet1").Range("A2:A4")
    For Each MyCell In Rng
        If MyCell <> "" Then
            myFile = Left(MyCell, InStr(MyCell, "\") - 1)
            myType = Mid(MyCell, InStr(MyCell, "\") + 1)
            MyCell.Offset(0, 1) = GetFileProperty(myFile, myType)
        End If
    Next
End Sub

Open in new window

nassio1985Asked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
Actually, repace the first function with this so it won't crash if the file does not exist. But remember that the names in row 1 must match the ones used in the code exactly.

Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    If oFS.FileExists(myFile) Then
        Select Case UCase(Trim(myType))
            Case "CREATED"
                GetFileProperty = oFS.GetFile(myFile).DateCreated
            Case "MODIFIED"
                GetFileProperty = oFS.GetFile(myFile).DateLastModified
            Case "ACCESSED"
                GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
            Case "SIZE"
                GetFileProperty = oFS.GetFile(myFile).Size
            Case Else
                GetFileProperty = "txt"
        End Select
    Else
        GetFileProperty = "File does not exist"
    End If
End Function

Open in new window

0
 
TommySzalapskiCommented:
  myFile = Left(MyCell, InStr(MyCell, "\") - 1)
This will pull the C: off each one and nothing else
  myType = Mid(MyCell, InStr(MyCell, "\") + 1)
This will pull everything after the first \

Shouldn't the select case be using the type not the filename?
0
 
TommySzalapskiConnect With a Mentor Commented:
Try this code and make sure the things in row 1 are EXACTLY the same as the cases (so row 1 starting at column B should be CREATED MODIFIED ACCESSED SIZE)

Here's my code which works if all the files exist.
Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Select Case UCase(Trim(myType))
        Case "CREATED"
            GetFileProperty = oFS.GetFile(myFile).DateCreated
        Case "MODIFIED"
            GetFileProperty = oFS.GetFile(myFile).DateLastModified
        Case "ACCESSED"
            GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
        Case "SIZE"
            GetFileProperty = oFS.GetFile(myFile).Size
        Case Else
            GetFileProperty = "txt"
    End Select
End Function

Public Sub SperiamoLoop()
    Dim MyCell As Variant, Rng As Range
    Dim myFile As String, myType As String
    Set Rng = Sheets("Sheet1").Range("A2:A4")
    For Each MyCell In Rng
        myFile = MyCell.Value
        If myFile <> "" Then
            For i = 1 To 4
                myType = Sheets("Sheet1").Range("A1").Offset(0, i)
                MyCell.Offset(0, i) = GetFileProperty(myFile, myType)
            Next
        End If
    Next
End Sub

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
AnthonyHamonCommented:
The code you attach has several bugs.  The code attached is much closer.
Sub SperiamoLoop()
Dim MyCell As Variant
Dim MyRng As Range
Dim MyFile As String
Dim MyType As String
Dim i As Integer
Dim oFS As Object
Set MyRng = Range("A2:A4")
For Each MyCell In MyRng
    i = Len(MyCell)
    Do While i > 0
        If Mid(MyCell, i, 1) = "." Then
            MyCell.Offset(0, 1).Value = Mid(MyCell, i + 1, 255)
            Exit Do
        End If
        i = i - 1
    Loop
     'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")
    MyCell.Offset(0, 2) = oFS.GetFile(MyCell).DateCreated
    MyCell.Offset(0, 3) = oFS.GetFile(MyFile).DateLastModified
Next
End Sub

Open in new window

0
 
nassio1985Author Commented:
AnthonyHamon:I used your code but it does wipe out some cell, please have a look on the screen shot have a look
0
 
nassio1985Author Commented:
sorry the above comment was for TommySzalapsk
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.

All Courses

From novice to tech pro — start learning today.