Path not found when creating a file-VBA

Question

I'm trying to create a text file and write data to it, simple right. Well it's not working and I've looked everywhere for it but can't find an answer.

When it gets to the CreateTextFile() method it throws a path not found error. But I've made sure the path is valid and exists.

'Create a text file
Private Sub OpenFile()
Dim filePath As String
Dim fileName As String
Dim fullPath As String
Const ForAppending = 8, TristateFalse = 0
Dim curDate As Date
Dim strDate As String
curDate = Date
strDate = CStr(curDate)
fileName = "DCSSInputLitigation_" & "(" & strDate & ")" & ".txt"

filePath = "C:\TempFolder\"

Set fs = CreateObject("Scripting.FileSystemObject")
fullPath = fs.BuildPath(filePath, fileName)

Set fWriter = fs.CreateTextFile(fullPath)

End Sub

When I hard code the path in the method it works but not when I use variables. Any Ideas?

  Set fWriter = fs.CreateTextFile("C:\TempFolder\test.txt")

enderland · Accepted Answer · 2014-06-12 20:03:59Z

4

When you get the date as follows:

strDate = CStr(curDate)

you are adding / into the file name and the string value for fullPath which you create is:

C:\TempFolder\DCSSInputLitigation_(6/12/2014).txt

File names cannot have / in them on Windows so you are running into problems here.

You can either format the date or replace the / like:

strDate = replace(CStr(curDate),"/","-")
strDate = Format(curDate,"dd-mm-yyyy")

Either will work.

answered Jun 12, 2014 at 20:03

enderland

14.2k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Path not found when creating a file-VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related