create a .txt file

[Tezzies]

Simple process I guess. All i want is to create a text file, name it, then save it.


I can open notepad using the shell command but i cant save the little fella afterwards.

Many thanks

[Bob Phillips]

Why not just create a simple Excel file and save it as a text file?

[Tezzies]

I want to keep the size and contents of the .txt to a minimum ie. 0 or 1 bytes . I know this seems picky but I am restricted with the amount of space available.

[lynnnow]

what exactly do you wish to do from VBA that the file is created with not more than 0 or 1 bytes?

Is it just creating a txt file from Excel without data in it, or is it a text file with data not more than 0 or 1 bytes?

[Bob Phillips]

Sorry, I don't understand how that impacts my proposed solution.

[Tezzies]

sry my explanation is a bit pants. If you re-name a excel spreadsheet to .txt the size of the .txt is above 1 byte. if you create a a .txt file from fresh and name it test.txt its size is 0 byte.

[Tezzies]

I would (using VBA) like to create a .txt called test.txt from fresh.

[lynnnow]

Is there a particular file name you want, based on the data available in your Excel file or is it a random file?

[Bob Phillips]

As I said, do it in Excel, save it a s text file. Do all this with the macro recorder on, and you have the code.

[Paul_Hossler]

One way -- Help has good writeup on the VBA cmds

[VBA]
Sub MakeTXT()
Const sFilename As String = "C:\SmallFile.txt"

Dim sLine As String
Dim iFile As Long

On Error Resume Next
Call Kill(sFilename)
On Error GoTo 0

iFile = FreeFile
Open sFilename For Output As iFile

sLine = "ABC"

Print #1, sLine

Close #1

End Sub
[/VBA]

Paul

[malik641]

This doesn't make much sense. If you cannot exceed 1 byte, then you can ONLY enter ONE character per text file. Is that what you really want?

That's just a bit strange.

[ALe]

sry my explanation is a bit pants. If you re-name a excel spreadsheet to .txt the size of the .txt is above 1 byte. if you create a a .txt file from fresh and name it test.txt its size is 0 byte.

Tezzies, the size of a txt file is 0 as long it's empty.

[ProteanBeing]

in Paul's example just change sLine = "ABC" to sLine = ""

[malik641]

in Paul's example just change sLine = "ABC" to sLine = ""

Almost, but that just makes it 2 bytes instead of 5 bytes. This is because 'Print #1, sLine' will always add a carriage return (or line feed, not sure which) and that takes 2 bytes. Write #1 will also add a line feed / carriage return.

I'm not sure how you would write to a text file to have ONLY one character without inherently adding a line feed to it...

[malik641]

Ok. To write to a text file so you do not have a carriage return at the end, use the FileSystemObject and TextStream objects (set a reference to Microsoft Scripting Runtime).

Here's the code:
[VBA]' SET REFERENCE TO 'Microsoft Scripting Runtime'
Sub MakeTXT()
Dim fso As New FileSystemObject
Dim myFile As TextStream

Set myFile = fso.CreateTextFile("C:\MyTextFile.txt", Overwrite:=True)

myFile.Write "1"

myFile.Close

End Sub[/VBA]

[ProteanBeing]

Open "U:\textfile.txt" For Output As #1
Close #1

0 bytes!

too easy to see

[malik641]

Open "U:\textfile.txt" For Output As #1
Close #1

0 bytes!

too easy to see

Right. But try writing 1 byte of data with that method.

[rlv]

I'm curious. Why do you want to create a 0-1 byte file?

[VBA]Public Sub EmptyTxtFile()
Const Outfile = "c:\test.txt"

Open Outfile For Output Access Write As #1 ' Open text file for write
Close #1 ' Close file.
End Sub[/VBA]

[ProteanBeing]

Tezzies,
you do know that even if you create a 0-1 byte file the computer will still reserve a sector for that file (4 kb on my computer). If you create a lot of these files there will be a HUGE difference between the electronic used space and the physical used space.