MkDir problem ?!

[mitko007]

Hi,
i need some help with the following problem:

i have a small script that creates a folder and creates some text files in it out of excel sheets (it simply prints the contents into a txt files). The thing is, if i have already created everything once, i get an error message on the second run since my Folder and files are already existing so it doesnt allow me to overwrite them. I would like to use something to enable overwriting the Folder and the containing files.


a would appreciate if someone could help me here,
Thanks

[mdmackillop]

You can delete files first using the Kill command

[Bob Phillips]

Error wrap it

[vba]

On Error Resume Next
MkDir file_path
On Error Goto 0
[/vba]

[mitko007]

Thanks for the replies,
i managed to delete the existing files and folders. But i come to the next issue:

im using the following functions to create my files:
[VBA]
'--------------------------
Function WriteText(ByVal strPathAndFilename As String, ByRef strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)
If Len(strTemp) > 0 Then
If blnOverWrite Then
Kill strPathAndFilename
Else
WriteText = False
Exit Function
End If
End If
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
Put #i, , strToWrite
Close #i
WriteText = True
End Function
'--------------------------------------------------
Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = " ", Optional ByVal strNewLine As String = vbCrLf) As String
Dim varArray As Variant
Dim i As Long, j As Long
Dim strTemp As String

If rng.Count = 1 Then
MakeText = rng.Value
Exit Function
Else
varArray = rng.Value
For i = 1 To UBound(varArray, 1)
For j = 1 To UBound(varArray, 2)
strTemp = strTemp & varArray(i, j) & strDelim
Next j
strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
Next i
strTemp = Left(strTemp, Len(strTemp) - 1)
MakeText = strTemp
End If
End Function

'---------------------------
[/VBA]
i implement them in a loop where before starting i check if the Folders/Files already exist and if so i delete them.

in my script i then run the follwoing lines:

strMyTextIWantToWrite = MakeText(Worksheets("Test").UsedRange, " ")
strFile = "C:\TEST\" & test.xml"
WriteText strFile, strMyTextIWantToWrite, True

the thing is doesn't work every time. On the second attepmt i get an error "76 Path not found" which directs me to the line

Open strPathAndFilename For Binary Access Write As #i

if i stop the dubger and run everything again its then ok.

I'm not sure if i was clear with my explanation but i hope for some help.

I'm new to programming and these functions i found online.

Thanks in advance

[SamT]

I don't know for sure what you are trying to do, but the comments I put in your code tell what you are actually doing.
[vba]
Function WriteText(ByVal strPathAndFilename As String, ByRef strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)

'If file exists then kill it or exit function
If Len(strTemp) > 0 Then
If blnOverWrite Then
Kill strPathAndFilename
Else
WriteText = False
'Try removing "Exit Function"
Exit Function
End If
End If

'File does not exist! Write it.
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
Put #i, , strToWrite
Close #i
WriteText = True
End Function [/vba]

[mitko007]

I don't know for sure what you are trying to do, but the comments I put in your code tell what you are actually doing.
[vba]
Function WriteText(ByVal strPathAndFilename As String, ByRef strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)

'If file exists then kill it or exit function
If Len(strTemp) > 0 Then
If blnOverWrite Then
Kill strPathAndFilename
Else
WriteText = False
'Try removing "Exit Function"
Exit Function
End If
End If

'File does not exist! Write it.
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
Put #i, , strToWrite
Close #i
WriteText = True
End Function [/vba]

Well its obviously not able to write it for some reason. Strangely it works on the first run (creates the files) than if i re-run it (deletes the existing files - this works) i get an error 76 Path.....After stopping the debuger und runing it again its ok (deletes existing files and folders and creates new ones)....so Run 1,3,5 will bi ok an in betweeen i always have a error Run.

[SamT]

You should always use Option Explicit. It will help you find errors in the code.

Because you start reading the array at i,j = 1, Option Base 1 sets the array indexes to start at 1. The default Array index start number is 0.

The following code is only an example. It won't work without being rewritten for your code.

The logic is:
1) As soon as you decide to write a new file, check for and Kill any existing file.
1b) At this time, you will have to somehow determine whether to overwrite the file. Maybe by Date file created. Maybe by an external Variable. Tell us what criteria you use and we can help code for that. If you can perform all appends by looping inside this procedure, you should be OK.
2) If any Error occurs, set the Public failure variable, "TextWritten," to False and End Sub.
3) Create the complete string to write using your Function "MakeText."
4) Write the Text to the File.
5) Repeat from "3" as needed, to append to the file. You'll need a vbCrLf between appends.
6) If no error happened, set "TextWritten" to True and exit sub.

[vba]
Option Explicit
Option Base 1
Public TextWritten As Boolean
Sub Selecting_Ranges_to_Write_To_File()
Dim i As Integer
Dim strToWrite As String
Dim strPathAndFilename As String
'You have to assign Value to strPathAndFilename
On Error GoTo ErrorHandler
'Check if file already exists
If Len(Dir(strPathAndFilename)) > 0 Then Kill strPathAndFilename
'Create the string to write
strToWrite = strToWrite & MakeText(rng#1,) & vbCrLf
strToWrite = strToWrite & MakeText(rng#2,) & vbCrLf
strToWrite = strToWrite & MakeText(rng#3,) & vbCrLf
'Write the string
'I don't know where the value of "FreeFile" is assigned
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
Put #i, , strToWrite
Close #i
TextWritten = True
Exit Sub 'Everything worked. Yay!
ErrorHandler:
TextWritten = False 'Something's wrong. Oops!
End Sub
'--------------------------------------------------
Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = " ") As String
Dim strTemp As String
Dim varArray
varArray = rng.Value
For i = 1 To UBound(varArray, 1)
For j = 1 To UBound(varArray, 2)
strTemp = strTemp & varArray(i, j) & strDelim
Next j
strTemp = Left(strTemp, Len(strTemp) - 1) & vbCrLf
Next i
strTemp = Left(strTemp, Len(strTemp) - 1)
MakeText = strTemp
End Function
[/vba]

Ok, I see by your last post, written whilst I was writing this post, that this won't solve your problem.

Go ahead and put "Option Explicit" and "Option Base 1" at the top of your code page. Then Compile the project repeatedly until you've fixed the problems revealed by "Option Explicit."

After it compiles without error, run it and let us know what line it errors on while running.

[mitko007]

will try now