Enumerate cell formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ScottS - 24 May 2008 04:09 GMT

I have a client that creates Excel reports by using links to a large master
spreadsheet. The reports are constantly changing and needless to say the
process of checking to be sure that the links are referencing the right cells
in the master sheet is a painful task.

I am looking for a way to list the external references in worksheet formulas
to a text file that I can import into Access. I can then match these
references against a table of cell references in the master table.

I could use some help with this - my VBA experience is primarily in Access.
Is there an easier way to do the same thing?

Thanks in advance for your help.

Scott

Signature

Scott S

Reply to this Message

Joel - 24 May 2008 05:05 GMT

the code below finds all formulas on Sheet1 and saves the cell location and
formula in a text file.

Sub saveformulas()

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

Folder = ThisWorkbook.Path

ChDir (Folder)
FName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")

If FName <> False Then
fswrite.CreateTextFile FName
Set fwrite = fswrite.GetFile(FName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

With Sheets("Sheet1")
Set Allformulas = _
.Cells.SpecialCells(Type:=xlCellTypeFormulas)
For Each cell In Allformulas
tswrite.writeline _
cell.Address & "," & cell.Formula
Next cell
End With
End If

End Sub

> I have a client that creates Excel reports by using links to a large master
> spreadsheet. The reports are constantly changing and needless to say the
[quoted text clipped - 11 lines]
>
> Scott

Reply to this Message

Tim Zych - 24 May 2008 06:04 GMT

Depending on your data structure, you may be able to benefit from my
freely-available workbook comparison utility. It can compare cell values or
formulas and produces an Excel report of the differences. You can compare
one or more columns and map them to any other columns in another worksheet.

www.higherdata.com

Signature

Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

>I have a client that creates Excel reports by using links to a large master
> spreadsheet. The reports are constantly changing and needless to say the
[quoted text clipped - 14 lines]
>
> Scott

Reply to this Message