Kill Filename Does not Kill, Appends Instead

B

biocentrism

Board Regular
Joined
Jul 28, 2014
Messages
187
I have the following code which is supposed to create a text file. Despite the Filename existing, the program does not delete the file before creating it. It Appends to the existing text file so that the text keeps getting added each time I export. Any ideas how to get this working? I am new to VBA and just trying to debug code someone else has written. Thank you!

Code: 
Sub CreateTextFile ()

Dim Output As String
Dim Filename As String

Filename = ThisWorkbook.Path & "\AccountList.txt"

'Deletes File if it Exists <---not working, it seems it is either not triggering an error or is not deleting
On Error Resume Next
Kill Filename
On Error Goto 0

'Write Output to File
Open Filename & ".txt" For Append As #1
Print #1, Output
Close #1

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi,

Set your PC to show file extensions, always useful when manipulating files, and you will see the problem.

AccountList.txt.txt.txt etc



Code: 
Open Filename [COLOR="#FF0000"]& ".txt"[/COLOR] For Append As #1

--->Open Filename  For Append As #1
 
Last edited:
Upvote 0
Possibly a stupid question, but why are you using "Open ... for Append" if you don't want to append data. If you didn't use Append, you wouldn't need to kill the file.

Regards, TMS
 
Upvote 0
Just to follow up:

Excel VBA Programming - Write To A Text File

Open FilePath For Output As #1


VBA will try to open up your file. If a file of the one named in FilePath doesn't exist it will be created for you. If the file does exist then it will be overwritten. (If you want the new contents to be added to the end of your file then you would use Append instead of Output.)


Regards, TMS
 
Upvote 0
TMShucks said:
Just to follow up:

Excel VBA Programming - Write To A Text File

Open FilePath For Output As #1


VBA will try to open up your file. If a file of the one named in FilePath doesn't exist it will be created for you. If the file does exist then it will be overwritten. (If you want the new contents to be added to the end of your file then you would use Append instead of Output.)


Regards, TMS
Click to expand...

Yes I was wondering that same thing myself. It is not code I wrote, I am just trying to debug it and I am a noob. I will give this a shot now. Thank you!
 
Upvote 0
TMShucks said:
Just to follow up:

Excel VBA Programming - Write To A Text File

Open FilePath For Output As #1


VBA will try to open up your file. If a file of the one named in FilePath doesn't exist it will be created for you. If the file does exist then it will be overwritten. (If you want the new contents to be added to the end of your file then you would use Append instead of Output.)


Regards, TMS
Click to expand...

Thank you for the link it looks like a great resource! So I have tried the code with the difference being I created the Output in a loop outside of the Write command.

I have checked the Output by using a MsgBox. However, when I create the file, the file is encapsulates the text in " " quote symbols. How do I get the text file to simply have the data without the quote symbols? Code is below. Thank you again!

Code: 
With wsSheet
For i = 7 to LastRow
Output = Output & .Range("AG" & i).Value
If i < LastRow Then
Output = Output & vbNewLine
End If
Next i
End With

Filename = "C:\TestFile.txt"

Open Filename For Output As #1
MsgBox (Output)
Write #1, Output
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Kill Temp File
Replies
1
Views
331
ExcelToDAX
E
J
  • Solved
Emailing active WS as Excel attachment failing
Replies
5
Views
256
NoSparks
N
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
461
Alex Blakenburg
A
E
  • Solved
How to refer to worksheet within a custom Excel add-in?
Replies
5
Views
202
eurobonds
E
V
  • Question
Delete files
Replies
4
Views
221
vmjan02
V

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top