PDA

View Full Version : [SOLVED] Create new text files using Cell values as titles



xltrader100
10-18-2013, 11:57 AM
I have a list of names in Column 1 on Sheet1 in an open workbook, and I'd like to create the same number of new empty text files in the folder at myFolderPath, using each name on the list as a file name. How do I do that?

mrojas
10-18-2013, 04:08 PM
This might just work.
In a loop traversing all the rows in column 1, call the following function passing it

Public Sub s_CreateFile(strFileNameWithPath as String)
' Date: 12/21/00
' Author: Milton Rojas
' Purpose: Writes the contents of a global string variable to a sequential file
' Called by: TryConnectingAgain,
' Calls: None
' Last Update:1/2/01

On Error GoTo FileWriteError
Open strFileNameWithPath For Output As #1
Print #1, ""
ExitPoint:
Exit Sub
FileWriteError:
MsgBox "Unable to create file!", vbExclamation, "Write File"
GoTo ExitPoint
End Sub

xltrader100
10-18-2013, 06:44 PM
Thanks, Milton. That's exactly what I needed. I have a further question about the line
Print #1, ""

That looked like a good place to enter a line of text into the empty text file, only it doesn't work. How would I write some text into each new text file as part of this same operation, i.e. without opening the new file?

EDIT:
It's ok, I finally got down to the 'Print #' item in the VBA Help index. I'd been floundering around higher up the list.

snb
10-19-2013, 07:36 AM
or


Sub M_snb()
sn=sheets("sheet1").columns(1).specialcells(2)

with createobject("scripting.filesystemobject")
for j=1 to ubound(sn)
.createtextfile("G:\OF\" & sn(j,1)).write "some text"
next
end with
End Sub