Checking if destination exists when using CreateTextFile

Question

I am making a form that the user fills out and when complete, they click a command button and the text they inputted is saved as a text file in a folder in the C: drive called Survey Results.

Currently, the user has to create the file "Survey Results" manually, and if it does not exist in the C: drive, there is an error in Word. I want some way to check if the destination exists before this error occurs and either prompt the user to create the folder or automatically create the folder.

What would I include in my while loop to check for this?

do while ()
messagebox1.open
loop




Private Sub CommandButton1_Click()
Dim FS As FileSystemObject
Set FS = New FileSystemObject
Dim MyFile As TextStream
Dim i As Integer
Dim FilePrefix As String, FileName As String, Extension As String

If CommandButton1.Enabled = True Then


FilePrefix = "C:\SurveyResults\"
Extension = ".txt"
i = 1

FileName = FilePrefix & comop & Trim(Str(i)) & Extension
Do While (FS.FileExists(FileName))
    i = i + 1
    FileName = FilePrefix & comop & Trim(Str(i)) & Extension
Loop

Set MyFile = FS.CreateTextFile(FileName)
MyFile.Write num1 & vbNewLine & num2 & vbNewLine & _
num3 & vbNewLine & num4 & vbNewLine & _
num5 & vbNewLine & num6 & vbNewLine & _
num7 & vbNewLine & num8 & vbNewLine & num9 & vbNewLine & num10 & vbNewLine & num11 _
& vbNewLine & num12 & vbNewLine & num13 & vbNewLine & num14 & vbNewLine _
& num15 & vbNewLine & num16 & vbNewLine & num17 & vbNewLine & num18 & vbNewLine & num19 & vbNewLine _
& num20 & vbNewLine & num21 & vbNewLine & num22 & vbNewLine & num23 & vbNewLine _
& num24 & vbNewLine & num25 & vbNewLine & num26 & vbNewLine & num27 & vbNewLine & num28 & vbNewLine _
& num29 & vbNewLine & num30 & vbNewLine & num31 & vbNewLine & num32 & vbNewLine & num33 & vbNewLine _
& com1a & vbNewLine & com1b & vbNewLine & com1c & vbNewLine & com1d & vbNewLine & com1e & vbNewLine _
& com1f & vbNewLine & com1g & vbNewLine & com1i & vbNewLine & com1j & vbNewLine & com1k & vbNewLine _
& com2a & vbNewLine & com2b & vbNewLine & com2c & vbNewLine & com2d & vbNewLine & com2e & vbNewLine _
& com2f & vbNewLine & com2g & vbNewLine _
& com3a & vbNewLine & com3b & vbNewLine & com3c & vbNewLine & com3d & vbNewLine & com3e & vbNewLine _
& com3f & vbNewLine _
& com4a & vbNewLine & com4b & vbNewLine & com4c & vbNewLine & com4d & vbNewLine & com4e & vbNewLine _
& com4f & vbNewLine & com4g & vbNewLine & com4h & vbNewLine & com4i & vbNewLine & com4j & vbNewLine & com4k & vbNewLine & com4l & vbNewLine _
& com5a & vbNewLine & com5b & vbNewLine & com5c & vbNewLine & com5d & vbNewLine & com5e & vbNewLine _
& com5f & vbNewLine & com5g & vbNewLine & com5h & vbNewLine & com5i & vbNewLine & com5j & vbNewLine _
& com6a & vbNewLine & com6b & vbNewLine & com6c & vbNewLine & com6d & vbNewLine _
& com1hb & vbNewLine & com1hd & vbNewLine _
& com1hf & vbNewLine & com1hh & vbNewLine & com1hj & vbNewLine & comop
End If

End Sub

http://stackoverflow.com/a/11573970/797393 – Cylian Aug 05 '13 at 13:55 — Cylian, Aug 05 '13 at 13:55

score 0 · Answer 1 · answered Aug 05 '13 at 14:01

Do something like the below snippet:

Dim FilePrefix$, fs As New Scripting.FileSystemObject
FilePrefix = "C:\SurveyResults\"
If Dir(FilePrefix, vbDirectory) <> "" Then
    'directory exists
Else
    'directory does not exist
    fs.CreateFolder FilePrefix         ' create the dir
End If

Checking if destination exists when using CreateTextFile

1 Answers1