software

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(quot;A1quot;).Text
strFileName = Range(quot;B1quot;).Text
strPath = Range(quot;C1quot;).Text

Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
Set ts = fso.CreateTextFile(strPath amp; 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

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(quot;Scripting.FileSystemObjectquot;)

For Each cell In Range(quot;A1:A10quot;)
strCellContents = cell.Text
strFileName = cell.Text
strPath = cell.Text
Set ts = fso.CreateTextFile(strPath amp; 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)

quot;Big Ianquot; lt;Big gt; wrote in message
...
gt; Hi,
gt;
gt; I have the following routine that makes text in cell A1 into the contents
of
gt; a file defined by B1 and C1.
gt;
gt; -------------------------------------------------------
gt; Sub WriteCellToFile()
gt; Dim fso As Object
gt; Dim ts As Object
gt; Dim strCellContents As String
gt; Dim strFileName As String
gt; Dim strPath As String
gt;
gt; strCellContents = Range(quot;A1quot;).Text
gt; strFileName = Range(quot;B1quot;).Text
gt; strPath = Range(quot;C1quot;).Text
gt;
gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; ts.writeline (strCellContents)
gt; ts.Close
gt;
gt; End Sub
gt; -------------------------------------------------------
gt;
gt; I tried running it with Run-Sub/User Form but I can only get it to work on
gt; one line, if I change the range to A1:A10 I get an error. Can anybody tell
me
gt; how to make it into a macro that writes all cells in column A to files?
gt;
gt;
gt; Thanks,
gt; Ian
Hi Bob,

Thanks for your help. I tried to run the routine but I got an error on line:

Set ts = fso.CreateTextFile(strPath amp; strFileName, True)

Run-time error '76'
Path not found

Also, I need each line to create a different file. The files are defined in
columns B and C, B is the name and C is the path.

Ianquot;Bob Phillipsquot; wrote:

gt; Sub WriteCellToFile()
gt; Dim fso As Object
gt; Dim ts As Object
gt; Dim strCellContents As String
gt; Dim strFileName As String
gt; Dim strPath As String
gt; Dim cell As Range
gt;
gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt;
gt; For Each cell In Range(quot;A1:A10quot;)
gt; strCellContents = cell.Text
gt; strFileName = cell.Text
gt; strPath = cell.Text
gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; ts.writeline (strCellContents)
gt; ts.Close
gt; Next cell
gt;
gt; Set ts = Nothing
gt; Set fso = Nothing
gt;
gt; End Sub
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Big Ianquot; lt;Big gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; I have the following routine that makes text in cell A1 into the contents
gt; of
gt; gt; a file defined by B1 and C1.
gt; gt;
gt; gt; -------------------------------------------------------
gt; gt; Sub WriteCellToFile()
gt; gt; Dim fso As Object
gt; gt; Dim ts As Object
gt; gt; Dim strCellContents As String
gt; gt; Dim strFileName As String
gt; gt; Dim strPath As String
gt; gt;
gt; gt; strCellContents = Range(quot;A1quot;).Text
gt; gt; strFileName = Range(quot;B1quot;).Text
gt; gt; strPath = Range(quot;C1quot;).Text
gt; gt;
gt; gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt; ts.writeline (strCellContents)
gt; gt; ts.Close
gt; gt;
gt; gt; End Sub
gt; gt; -------------------------------------------------------
gt; gt;
gt; gt; I tried running it with Run-Sub/User Form but I can only get it to work on
gt; gt; one line, if I change the range to A1:A10 I get an error. Can anybody tell
gt; me
gt; gt; how to make it into a macro that writes all cells in column A to files?
gt; gt;
gt; gt;
gt; gt; Thanks,
gt; gt; Ian
gt;
gt;
gt;

Sorry, forgot my offsets. Try this

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(quot;Scripting.FileSystemObjectquot;)

For Each cell In Range(quot;A1:A10quot;)
strCellContents = cell.Text
strFileName = cell.Offset(0, 1).Text
strPath = cell.Offset(0, 2).Text
Set ts = fso.CreateTextFile(strPath amp; 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)

quot;Big Ianquot; gt; wrote in message
...
gt; Hi Bob,
gt;
gt; Thanks for your help. I tried to run the routine but I got an error on
line:
gt;
gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt;
gt; Run-time error '76'
gt; Path not found
gt;
gt; Also, I need each line to create a different file. The files are defined
in
gt; columns B and C, B is the name and C is the path.
gt;
gt; Ian
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Sub WriteCellToFile()
gt; gt; Dim fso As Object
gt; gt; Dim ts As Object
gt; gt; Dim strCellContents As String
gt; gt; Dim strFileName As String
gt; gt; Dim strPath As String
gt; gt; Dim cell As Range
gt; gt;
gt; gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; gt;
gt; gt; For Each cell In Range(quot;A1:A10quot;)
gt; gt; strCellContents = cell.Text
gt; gt; strFileName = cell.Text
gt; gt; strPath = cell.Text
gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt; ts.writeline (strCellContents)
gt; gt; ts.Close
gt; gt; Next cell
gt; gt;
gt; gt; Set ts = Nothing
gt; gt; Set fso = Nothing
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Big Ianquot; lt;Big gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I have the following routine that makes text in cell A1 into the
contents
gt; gt; of
gt; gt; gt; a file defined by B1 and C1.
gt; gt; gt;
gt; gt; gt; -------------------------------------------------------
gt; gt; gt; Sub WriteCellToFile()
gt; gt; gt; Dim fso As Object
gt; gt; gt; Dim ts As Object
gt; gt; gt; Dim strCellContents As String
gt; gt; gt; Dim strFileName As String
gt; gt; gt; Dim strPath As String
gt; gt; gt;
gt; gt; gt; strCellContents = Range(quot;A1quot;).Text
gt; gt; gt; strFileName = Range(quot;B1quot;).Text
gt; gt; gt; strPath = Range(quot;C1quot;).Text
gt; gt; gt;
gt; gt; gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt; gt; ts.writeline (strCellContents)
gt; gt; gt; ts.Close
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt; -------------------------------------------------------
gt; gt; gt;
gt; gt; gt; I tried running it with Run-Sub/User Form but I can only get it to
work on
gt; gt; gt; one line, if I change the range to A1:A10 I get an error. Can anybody
tell
gt; gt; me
gt; gt; gt; how to make it into a macro that writes all cells in column A to
files?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Ian
gt; gt;
gt; gt;
gt; gt;
Bob you're a legend! Many thanks again.

Ianquot;Bob Phillipsquot; wrote:

gt; Sorry, forgot my offsets. Try this
gt;
gt; Sub WriteCellToFile()
gt; Dim fso As Object
gt; Dim ts As Object
gt; Dim strCellContents As String
gt; Dim strFileName As String
gt; Dim strPath As String
gt; Dim cell As Range
gt;
gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt;
gt; For Each cell In Range(quot;A1:A10quot;)
gt; strCellContents = cell.Text
gt; strFileName = cell.Offset(0, 1).Text
gt; strPath = cell.Offset(0, 2).Text
gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; ts.writeline (strCellContents)
gt; ts.Close
gt; Next cell
gt;
gt; Set ts = Nothing
gt; Set fso = Nothing
gt;
gt; End Sub
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Big Ianquot; gt; wrote in message
gt; ...
gt; gt; Hi Bob,
gt; gt;
gt; gt; Thanks for your help. I tried to run the routine but I got an error on
gt; line:
gt; gt;
gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt;
gt; gt; Run-time error '76'
gt; gt; Path not found
gt; gt;
gt; gt; Also, I need each line to create a different file. The files are defined
gt; in
gt; gt; columns B and C, B is the name and C is the path.
gt; gt;
gt; gt; Ian
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Sub WriteCellToFile()
gt; gt; gt; Dim fso As Object
gt; gt; gt; Dim ts As Object
gt; gt; gt; Dim strCellContents As String
gt; gt; gt; Dim strFileName As String
gt; gt; gt; Dim strPath As String
gt; gt; gt; Dim cell As Range
gt; gt; gt;
gt; gt; gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; gt; gt;
gt; gt; gt; For Each cell In Range(quot;A1:A10quot;)
gt; gt; gt; strCellContents = cell.Text
gt; gt; gt; strFileName = cell.Text
gt; gt; gt; strPath = cell.Text
gt; gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt; gt; ts.writeline (strCellContents)
gt; gt; gt; ts.Close
gt; gt; gt; Next cell
gt; gt; gt;
gt; gt; gt; Set ts = Nothing
gt; gt; gt; Set fso = Nothing
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Big Ianquot; lt;Big gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi,
gt; gt; gt; gt;
gt; gt; gt; gt; I have the following routine that makes text in cell A1 into the
gt; contents
gt; gt; gt; of
gt; gt; gt; gt; a file defined by B1 and C1.
gt; gt; gt; gt;
gt; gt; gt; gt; -------------------------------------------------------
gt; gt; gt; gt; Sub WriteCellToFile()
gt; gt; gt; gt; Dim fso As Object
gt; gt; gt; gt; Dim ts As Object
gt; gt; gt; gt; Dim strCellContents As String
gt; gt; gt; gt; Dim strFileName As String
gt; gt; gt; gt; Dim strPath As String
gt; gt; gt; gt;
gt; gt; gt; gt; strCellContents = Range(quot;A1quot;).Text
gt; gt; gt; gt; strFileName = Range(quot;B1quot;).Text
gt; gt; gt; gt; strPath = Range(quot;C1quot;).Text
gt; gt; gt; gt;
gt; gt; gt; gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; gt; gt; gt; Set ts = fso.CreateTextFile(strPath amp; strFileName, True)
gt; gt; gt; gt; ts.writeline (strCellContents)
gt; gt; gt; gt; ts.Close
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt; -------------------------------------------------------
gt; gt; gt; gt;
gt; gt; gt; gt; I tried running it with Run-Sub/User Form but I can only get it to
gt; work on
gt; gt; gt; gt; one line, if I change the range to A1:A10 I get an error. Can anybody
gt; tell
gt; gt; gt; me
gt; gt; gt; gt; how to make it into a macro that writes all cells in column A to
gt; files?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; Ian
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;