Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Runtime error 5 in VBA loop

Posted on 2011-09-15
2
Medium Priority
?
323 Views
Last Modified: 2012-05-12
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 is giving me this error"Runtime error 5" in the lines where I put the @. Anyone knows how to make this function loop? 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

0
Comment
Question by:nassio1985
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36544313
Have you just put a forward slash in when you need a back slash?
0
 

Author Closing Comment

by:nassio1985
ID: 36544424
excellent!!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question