CODEWITHSUNDEEP
excelvbams-word
MRB
MRB

Reputation: 452

VBA FileFolderExists pass variable

I found this function on a web

    Private Function FileFolderExists(strFullPath As String) As Boolean

    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString then
        FileFolderExists = True
    End If

    EarlyExit:
    On Error GoTo 0
    End Function

And I want to pass string variable like this

    Dim lineText As String
    ...
    ElseIf FileFolderExists(lineText) = False Then

I am getting compile error "byref argument type mismatch"

When I put byval before strFullPath, it doesn't seem to work properly. I also tried playing with Dir function, it works if I pass literal like "C:\test", but it doesn't work if I pass the variable.

Does anyone have function that check for folder existence and accepts the string variable as parameter ?

Thanks in advance

Upvotes: 0

Views: 2718

Answers (3)

martin
martin

Reputation: 2638

The problem seems to be that Word adds CR character to every paragraph, or, to be more exact, that the Text property of the Paragraph object returns the paragraph text plus the CR character.

AFAIK, this is the Word's behaviour for every paragraph, even for the last one.

How can this cause a compile error, I do not have a clue. If I take Milan's example:

Private Sub FirstLineFolder()
Dim lineText As String

lineText = ActiveDocument.Paragraphs(1).Range.Text
lineText = Left(lineText, Len(lineText) - 1) 'see below

MsgBox DoesFolderExist("C:\") 
MsgBox DoesFolderExist(lineText)
End Sub

it returns true, true if the first line of the document is a valid folder. If I comment the marked line, the program still compiles and runs and returns true, false (with the same document).

There is some info about it on MSDN website

Upvotes: 1

MRB
MRB

Reputation: 452

New code, it explains exactly what I need, it should be easier for you to try.

I am expecting folder in first line of the Word document, then I have to check if it exists.

Private Sub FirstLineFolder()
Dim lineText As String

lineText = ActiveDocument.Paragraphs(1).range.Text

MsgBox DoesFolderExists("C:\") ' this works
MsgBox DoesFolderExists(lineText) ' this doesnt work, when same folder passed
End Sub

Both my and Martin's function are throwing compiling error I wrote in my first post.

If it matters : Word is 2010, "option explicit" isn't written (I inherited the code, I can't change that)

Upvotes: 0

martin
martin

Reputation: 2638

Try this:

Function FolderExists(folderPath As String) As Boolean

Dim f As Object
Set f = CreateObject("Scripting.FileSystemObject")

On Error GoTo NotFound
Dim ff As Object
Set ff = f.GetFolder(folderPath)
FolderExists = True
Exit Function

NotFound:
FolderExists = False
On Error GoTo 0

End Function

I used the following to test it:

Sub Tst()

Dim b As Boolean
Dim s As String
s = "c:\temp"
b = FolderExists(s)

End Sub

And it works as expected.

Generally, I used Scripting.FileSystemObject for all file-related operation in VBA, the native functions are too cumbersome.

It should be also noted that my function all checks for folders, while the original function -- judging by its name -- perhaps also tried to check for existence of files.

Upvotes: 0

Related Questions