Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Runtime error 5 in VBA loop

Posted on 2011-09-15
2
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

618 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