Add new text file in excel using VBA

[Ying]

Does anyone know if it's possible to add a new text file
and name it in Excel using VBA?

Thanks!

 

[Harlan Grove]

Does anyone know if it's possible to add a new text file
and name it in Excel using VBA?

What *precisely* do you mean by 'add a new text file'? There are many ways
to create text files from Excel using VBA.

 

[J.E. McGimpsey]

Look at SaveAs in VBA Help. One example:

Worksheets(1).SaveAs Filename:="test.txt", _
fileformat:=xlCurrentPlatformText

For examples of custom formats, you might want to take a look here:

http://www.mcgimpsey.com/excel/textfiles.html

 

[Ying]

Actually, What I need to do is output data into a text
file directly without displaying it first on a worksheet.
Your solution works if I have data in a worksheet, then
try to save it as test file.

 

[J.E. McGimpsey]

Output data from what?

If the data isn't in XL, why use XL to create a text file?

 

[Ying]

I have to use data from a workbook to create a 396x396
matrix "behind the scene" then output to a text file
(since Excel can't handle matrix that size). I did find a
round-about way to do it: create a text file outside of
excel first, then output the data. When I need to re-use
the text file, clear out the existing data, then output
new data. I'm just wondering if there is a way to create a
text file "on the fly" in VBA code.

Thanks!

 

[Harlan Grove]

Sub INSERTSUBNAME()
Dim fs, f
OutFile = INSERT NAME HERE (ie "C:\TEMP\mymatrix.txt")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(OutFile, 2, -2)
f.Close
Set f = Nothing
Set fs = Nothing
End Sub

...

More than one way to do this. It's possible that direct VBA I/O calls would be
faster than going through the FSO. That is,


fd = FreeFile
Open SomeFilenameHere For Output As #fd

For i = 1 To NumRows

For j = 1 To NumCols
Print #fd, SomethingDependingOn_i_and_j;
Next j

Print #fd,

Next i

Close #fd