Thread
:
How to use this subroutine on multiple cells?
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
Posts: n/a
How to use this subroutine on multiple cells?
Sub WriteCellToFile()
Dim fso As Object
Dim ts As Object
Dim strCellContents As String
Dim strFileName As String
Dim strPath As String
Dim cell As Range
Set fso = CreateObject("Scripting.FileSystemObject")
For Each cell In Range("A1:A10")
strCellContents = cell.Text
strFileName = cell.Text
strPath = cell.Text
Set ts = fso.CreateTextFile(strPath & strFileName, True)
ts.writeline (strCellContents)
ts.Close
Next cell
Set ts = Nothing
Set fso = Nothing
End Sub
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Big Ian" <Big
wrote in message
...
Hi,
I have the following routine that makes text in cell A1 into the contents
of
a file defined by B1 and C1.
-------------------------------------------------------
Sub WriteCellToFile()
Dim fso As Object
Dim ts As Object
Dim strCellContents As String
Dim strFileName As String
Dim strPath As String
strCellContents = Range("A1").Text
strFileName = Range("B1").Text
strPath = Range("C1").Text
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile(strPath & strFileName, True)
ts.writeline (strCellContents)
ts.Close
End Sub
-------------------------------------------------------
I tried running it with Run-Sub/User Form but I can only get it to work on
one line, if I change the range to A1:A10 I get an error. Can anybody tell
me
how to make it into a macro that writes all cells in column A to files?
Thanks,
Ian
Reply With Quote