Results 1 to 7 of 7

Thread: Run Time Error 52 Using Dir()

  1. #1

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Question Run Time Error 52 Using Dir()

    It's been a while. I've run into another problem that has me scratching my head. I did a search and didn't find anything that fit the problem.

    The basics.

    I need to be able to verify whether or not two files are present in a directory. If both are there, one set of options are accessed. If one is present and the other is not another set of options are accessed, depending on which file it is that is present. If neither is present then a message is sent telling people that this is the case.

    Now the problem

    I created a function to detect the present of the files, one at a time.

    Code:
    Public Function FileReal(RealFileName As String) As Boolean
         FileReal = Dir(RealFileName) > ""
    End Function
    And this is the code that is supposed to make use of the function.
    Code:
        
        Dim MyDocsPath As String, fileToOpen As String, fileToOpen2 As String
        Dim school1 As Workbook, school2 As Workbook
        
        MyDocsPath = Environ$("USERPROFILE") & "\My Documents\"
        fileToOpen = MyDocsPath & "school-raw data.xlsx"
        fileToOpen2 = MyDocsPath & "school-raw_data-SB.xlsx"
        
        If FileReal(fileToOpen) = True Then
            Workbooks.Open FileName:=fileToOpen
            Set school1 = ActiveWorkbook
            If FileReal(fileToOpen2) = True Then
                Workbooks.Open FileName:=fileToOpen2
                Set school2 = ActiveWorkbook
            Else
            End If
        ElseIf FileReal(fileToOpen2) = True Then
            Workbooks.Open FileName:=fileToOpen2
        Else
            MsgBox ("Your Raw data files are missing")
        End If
    Unfortunately, every time I have attempted to run the macro, I have gotten "Run Time Error: 52 Bad file name or number"; yet when I check what is being sent to the function the path and file name is correct.

    So, what am I doing wrong? Suggestions?
    If you have to do it more than once...
    Automate it!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Run Time Error 52 Using Dir()

    depending on os version
    your code works in XP, but in w10 there is no my in documents, but in either case no error occurred, when testing, just the file not found
    i also tested with open file, no error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Run Time Error 52 Using Dir()


  4. #4

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Run Time Error 52 Using Dir()

    Quote Originally Posted by westconn1 View Post
    depending on os version
    your code works in XP, but in w10 there is no my in documents, but in either case no error occurred, when testing, just the file not found
    i also tested with open file, no error
    Hmmm. An issue I may need to address in future.

    Currently I am running Win8.1, not sure what client is running, possibly Wn10. The thing is, that if I run the code without the function, that is without testing to see if the file is actually there, it runs fine. I only get the run time error when I use my UDF.

    That is, this works

    Code:
        Dim MyDocsPath As String, fileToOpen As String, fileToOpen2 As String
        
        MyDocsPath = Environ$("USERPROFILE") & "\My Documents\"
        fileToOpen = MyDocsPath & "school-raw data.xlsx"
        
         Workbooks.Open FileName:=fileToOpen
    This does not:
    Code:
        Dim MyDocsPath As String, fileToOpen As String, fileToOpen2 As String
        Dim school1 As Workbook, school2 As Workbook
        
        MyDocsPath = Environ$("USERPROFILE") & "\My Documents\"
        fileToOpen = MyDocsPath & "school-raw data.xlsx"
        fileToOpen2 = MyDocsPath & "school-raw_data-SB.xlsx"
        
        If FileReal(fileToOpen) = True Then
            Workbooks.Open FileName:=fileToOpen
    And it keeps breaking on the function. What is most frustrating is that, as I said (tho not as explicitly), when I get the error and click the [DEBUG] button, I have run the mouse pointer over the " FileReal = Dir(RealFileName) > """ line and it shows the correct file name and path for RealFileName but still tells me that I have "Bad file name or number". I just can't seem to see what I'm doing wrong. Especially if you are having no problem running it.

    Not sure what to try next.
    If you have to do it more than once...
    Automate it!

  5. #5

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Run Time Error 52 Using Dir()

    Thank for the link jdc2000. Unfortunately it doesn't seem to apply.
    If you have to do it more than once...
    Automate it!

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Run Time Error 52 Using Dir()

    Try this modified function:

    Code:
    Public Function FileReal(RealFileName As String) As Boolean
    
         On Error GoTo TrapError:
         FileReal = Dir(RealFileName) > ""
    FinishUp:
         On Error GoTo 0
         Exit Function
    TrapError:
         FileReal = False
         Resume FinishUp:
         
    End Function
    Last edited by jdc2000; May 3rd, 2017 at 02:08 PM.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Run Time Error 52 Using Dir()

    do you get the error if the file does not exist?

    the most probable cause of error is invalid character in file name, test with other filenames, or lack of permission to access the file, neither appear to be the case in your scenario

    test this alternative function
    Code:
    Public Function FileReal(RealFileName As String) As Boolean
    '     FileReal = Dir(RealFileName) > ""
          FileReal = CreateObject("scripting.filesystemobject").fileexists(RealFileName)
    End Function
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width