MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Write Cell contents in text file


Posted by Chief on August 15, 2000 7:37 AM

I need to create a macro to go through worksheets in a workbook and append their names to a text file based on what is in a particular cell. For example if the cell said TKManual, that worksheets name would be appended to a new line in a file called TKManual.txt

Any help is appreciated.


Posted by Michael Liu on August 15, 0100 9:24 AM

So let's say your file has 5 sheets, called
Sheet1 - Sheet5. And filenames are specified
in cell A1 in each sheet, and have values
file1-file5.

The desired output would then be 5 files which
only contain the corresponding worksheet name?
ie.
file1.txt contents:Sheet1
file2.txt contents:Sheet2
etc...

Is this the objective, and is the cell that designates
the filename on each sheet the same range? (All in cell A1
or whatever)

Posted by Michael Liu on August 15, 0100 11:31 AM

Here is an example that works given the assumptions in my last post.
You could change the saveDir reference and the two
"A1" references as needed.

Sub Export_WBNames()
saveDir = "H:\"
For Each ws In Worksheets
If Application.IsText(ws.Range("A1")) Then
targetFile = saveDir & ws.Range("A1").Value & ".txt"
If Dir(targetFile) <> "" Then Kill targetFile
Open targetFile For Output As #1
Print #1, ws.Name
Close #1
End If
Next
End Sub