Could it be done? Yeah, I bet so. But I wouldn't do it that way.

How about an alternative--just use the windows temp folder and you can clean it
up when you're doing regularly scheduled housekeeping:

Option Explicit
Dim myTextFileName As String
Sub runBoth()
Call fl
If Dir(myTextFileName) < "" Then
Shell "start " & myTextFileName
'Application.Wait Now + TimeSerial(0, 0, 2)
'Kill myTextFileName
End If
End Sub
Sub fl()
Dim fd As Variant, ws As Worksheet, c As Range

myTextFileName = Environ("temp") & "\" & ActiveWorkbook.Name & ".fl.txt"

fd = FreeFile
Open myTextFileName For Output As #fd
On Error GoTo CleanUp

For Each ws In ActiveWorkbook.Worksheets
Print #fd, ws.Name

For Each c In ws.UsedRange
If c.HasFormula Then
Print #fd, c.Address(0, 0)
Print #fd, "", c.Formula
Print #fd, "", c.FormulaR1C1
Print #fd, ""
End If
Next c

Print #fd, "------------"
Print #fd, ""
Next ws

CleanUp:
Close #fd
End Sub

If you want to save the output, you can save it from notepad (or whatever
program you have associated with .txt files.)

I came back and added this:

Application.Wait Now + TimeSerial(0, 0, 2)
Kill myTextFileName

(and commented them out.)

in Win98 and using Notepad, I could delete that text file while it was open in
notepad.

I'm not sure if that'll work in all versions of windows or text editors. (I'm
kind of surprised that it worked at all.)


Jack Sons wrote:

Dave,

What I want is the file with the formulas just on screen, not yet saved at
all. Probably I want to look at it and most likely I do not want to save it
anyhow.
Is that possible?

Jack.
"Dave Peterson" schreef in bericht
...
Harlan's line of code:

Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd

used the same folder that contained the excel file.

If you hadn't saved the file, it would save to the current directory of
the
activedrive. (Probably the Default file location if you haven't been
changing
things.)

You could put it pretty much anywhere you wanted:

Open "C:\my documents\" & activeworkbook.name & ".fl.txt" For Output As
#fd

(notice that .fullname was replaces with .name)

JE McGimpsey does something equivalent using a worksheet:
http://mcgimpsey.com/excel/listfunctions.html



Jack Sons wrote:

Harlan,

I found the resulting text file "somewhere", in just a Excel sub
directory.
I could not find any reason why it was saved in that particular
directory.
Would anyhow never have expected the "txt" file to be saved in an Excel
directory rather than in a Word directory.

This is not convenient, could you please revise the code in a way that
the
resulting file will be a map on screen, leaving it to me wether I like
to
save it somewhere or not.

Jack Sons
The Netherlands

"Harlan Grove" schreef in bericht
...
"Earl" wrote...
How do I print a list of all formulas contained in a worksheet?

Using a macro would be one way. Here's one that prints just formulas
in
both
A1 and R1C1 formats from the active workbook to text files.


Sub fl()
Dim fd As Variant, ws AS Worksheet, c As Range

fd = Freefile
Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd
On Error GoTo CleanUp

For Each ws In ActiveWorkbook.Worksheets
Print #fd, ws.Name

For Each c In ws.UsedRange
If c.HasFormula Then
Print #fd, c.Address(0,0)
Print #fd, "", c.Formula
Print #fd, "", c.FormulaR1C1
Print #fd, ""
End If
Next c

Print #fd, "------------"
Print #fd, ""
Next ws

CleanUp:
Close #fd
End Sub



--

Dave Peterson

--

Dave Peterson