UDF中的Unexpecter错误，该错误检查是否可以找到文件 [英] Unexpecter error from UDF which checks if a file can be found

问题描述

我的代码中有一个if语句:

 如果sharepointFileExists(sFullFilePath)或fileOnDisk(sFullFilePath)然后 

需要两个函数:

 函数fileOnDisk(ByVal strPath As String)As BooleanfileOnDisk = CBool​​(Len(Dir(strPath))> 0)结束功能 

和

 函数sharepointFileExists(ByVal strUrl As String)As Boolean在错误转到To ErrorHandlerDim oHttp作为对象设置oHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")oHttp.Open"HEAD"，strUrl，FalseoHttp.Send'Debug.Print oHttp.StatussharepointFileExists = CBool​​(oHttp.Status = 200)退出功能ErrorHandler:'Debug.Print Err.Number&"-"&错误说明'Debug.Print" Feil:-"&oHttp.StatussharepointFileExists = False结束功能 

我有几个使用此代码的文件，在其中一个文件中，即使所有输入均相似，也会出现错误.

在函数 fileOnDisk 中，出现错误

运行时错误'52':文件名或数字错误

提供给函数的参数类似于 https://nhy.sharepoint.com/teams/Team-xxxxxx/xxxx/xxxx/Excel-file.xlsx ，我希望 Dir 在磁盘上找不到，从而返回零长度的字符串(

有人知道这里发生了什么吗?

解决方案

如果父目录不存在，则 Dir()方法将引发错误.在这种情况下，您需要使用 FileSystemObject ，它只是返回一个布尔值.

 函数fileOnDisk(ByVal strPath As String)As Boolean与CreateObject("Scripting.FileSystemObject")一起使用fileOnDisk = .FileExists(strPath)结束于结束功能 

I have an if-statement in my code:

If sharepointFileExists(sFullFilePath) Or fileOnDisk(sFullFilePath) Then

which calls upon two functions:

Function fileOnDisk(ByVal strPath As String) As Boolean
    fileOnDisk = CBool(Len(Dir(strPath)) > 0)
End Function

and

Function sharepointFileExists(ByVal strUrl As String) As Boolean
    On Error GoTo ErrorHandler
    Dim oHttp As Object
    
    Set oHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    oHttp.Open "HEAD", strUrl, False
    oHttp.Send
    'Debug.Print oHttp.Status
    sharepointFileExists = CBool(oHttp.Status = 200)
    Exit Function
ErrorHandler:
    'Debug.Print Err.Number & " - " & Err.Description
    'Debug.Print "Feil: - " & oHttp.Status
    sharepointFileExists = False
End Function

I have several files which use this code, and in one of them I get an error even though the input should be similar for all of them.

In the function fileOnDisk, I get the error

Run-time error '52': Bad file name or number

The argument supplied to the function is something like https://nhy.sharepoint.com/teams/Team-xxxxxx/xxxx/xxxx/Excel-file.xlsx, which I would expect Dir to not find on the disk and thus return a zero-length string (as per the documentation). Instead I get the error described above.

Trying to print the argument to the immediate window when debugging the code gives the string value I'd expect.

Does anyone have an idea what's going on here?

解决方案

The Dir() method throws an error if the parent directory does not exist. You need to use the FileSystemObject in this case which simply returns a boolean.

Function fileOnDisk(ByVal strPath As String) As Boolean
    With CreateObject("Scripting.FileSystemObject")
        fileOnDisk = .FileExists(strPath)
    End With
End Function

这篇关于UDF中的Unexpecter错误，该错误检查是否可以找到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！