How to extract all formulas used in an excel file?

[bee123]

Hi


Is there any way to extract all formulas from the excel file.I have almost 10-25 excel files and it uses all sort of formulas ,and its difficult to check all the files to identify the formulas.


Any ideas?

 

[bobhc]

Good day

Keyboard Shortcut to Show Formulas in active work sheet in Excel


You can use the keyboard Shortcut CTRL +

to show formulas in Excel. (

Grave Accent key is the one below the Escape Key on the far upper left side of the keyboard)

 

[bee123]

thanks for the reply.But is there any way to EXTRACT ALL FORMULAS from an excel to text file or ?

 

[Hui]

Bee123


There is no native way to do what you have asked


There are a number of utilities or Excel Addins that allow this functionality

One such Addin that I use regularly is John Walkenbachs, PUP (Power Utility Pack)

http://spreadsheetpage.com/index.php/pupv7/home


Just be careful as with large spreadsheets this can produce huge reports

 

[SirJB7]

Hi, bee123!


Place this code into a module of the workbook from which you want to extract all the formulas (Alt-F11, Insert, Module, if none available).

Then Alt-F8, select macro X (you can change the name to whatever you like), and you'll get a workbook with the same name plus the suffix "_doc" with the same worksheets as of source and all the formulas.


-----

[pre]

Sub X()
' constants
Const ksDot = "."
Const ksDoc = "_doc"
Const ksXLSX = "xlsx"
' declarations
Dim I As Integer, J As Integer, K As Integer, L As Integer
Dim iRows As Integer, iColumns As Integer
Dim sName As String, sFileName As String
Dim sNewName As String, sNewFileName As String
' start
'  source workbook
With ActiveWorkbook
sName = .Name
sFileName = .FullName
I = InStr(StrReverse(sName), ksDot)
sNewFileName = Left$(sName, _
Len(sName) - I - 1) & ksDoc & ksDot & ksXLSX
I = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = .Worksheets.Count
End With
'  delete
If Dir(sNewFileName, vbNormal) <> "" Then Kill sNewFileName
'  add
Workbooks.Add
Application.SheetsInNewWorkbook = I
With ActiveWorkbook
.SaveAs sNewFileName
sNewName = .Name
End With
Workbooks(sName).Activate
' process
With ActiveWorkbook
For I = 1 To .Worksheets.Count
' format
Workbooks(sNewName).Worksheets(I).Cells.NumberFormat = "@"
' name
Workbooks(sNewName).Worksheets(I).Name = .Worksheets(I).Name
' formulas
With .Worksheets(I)
With .UsedRange
iRows = .Rows.Count
iColumns = .Columns.Count
End With
For K = 1 To iRows
For J = 1 To iColumns
Workbooks(sNewName).Worksheets(I).Cells(K, J).Value = .Cells(K, J).Formula
Next J
Next K
End With
Next I
End With
' end
End Sub

[/pre]
-----


Regards!

 

[bee123]

hi

thanks for the reply.


But I have 25 worksheets for 1 excel file.So its taking so much time for me,since I have to go to every page and run the code and get formula for every single page and copy to my destination.Is there any short way to put all the formulas in a single page ?

 

[SirJB7]

Hi, bee123!

Please read carefully what I posted. Once you placed the code (only 1 time, workbook), you run it once (only 1 time).

Regards!