-
May 2nd, 2017, 09:32 PM
#1
Thread Starter
Lively Member
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!
-
May 3rd, 2017, 05:16 AM
#2
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
-
May 3rd, 2017, 09:16 AM
#3
Re: Run Time Error 52 Using Dir()
-
May 3rd, 2017, 11:51 AM
#4
Thread Starter
Lively Member
Re: Run Time Error 52 Using Dir()
Originally Posted by westconn1
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!
-
May 3rd, 2017, 11:53 AM
#5
Thread Starter
Lively Member
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!
-
May 3rd, 2017, 01:11 PM
#6
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.
-
May 3rd, 2017, 04:31 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|