Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate cell formulas
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 -- Scott S |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate cell formulas
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 "ScottS" wrote: 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 -- Scott S |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate cell formulas
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 -- Tim Zych www.higherdata.com Compare data in workbooks and find differences with Workbook Compare A free, powerful, flexible Excel utility "ScottS" wrote in message ... 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 -- Scott S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enumerate DSNs | Excel Programming | |||
Compare multiple dates, can enumerate cell based on most recent date | Excel Worksheet Functions | |||
enumerate sheet | Excel Worksheet Functions | |||
how to enumerate? | New Users to Excel | |||
Is there any way to enumerate the cell which contain a formula? | Excel Programming |