How to remove source code, hidden links, Formula links from excel

[harikris2013]

Hi,


I am running a macro for data in excel but it is leaving the source code, formula links and hidden links after removing the vba script.


Can any one help me with the solution how to remove all the links from the excel becoz i am sending these files to third party where some software is detecting all the source and links in excel


Thank you in Advance.

 

[dispelthemyth]

Have you already;

1) - Pressed Alt + F11 and removed any VBA code that tries to contact your companies servers
2) - copied and pasted (special values) so that no formulas remain?

for 2, to remove the manual element you can use the below

If you have hidden sheets, this VBA will unhide all sheets and remove all formulas


Sub UnhideSheets()
'Start looping through all worksheets
For Each WS In ThisWorkbook.Worksheets

'makes all sheets visible
Application.ScreenUpdating = False
If WS.Name <> "RandomText" Then
WS.Visible = xlSheetVisible
End If
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next WS

End Sub

 

[harikris2013]

Thanks for your response,

> i am copy pasting only values from macro sheet to ordinary workbook but when i send it to third party some software from there end is detecting the source code for all cells in excel.

Attaching snap shot for reference.

 

[dispelthemyth]

Im not sure what you mean by
"some software from there end is detecting the source code for all cells in excel."

Can you give an example





the snap shot is not appearing

You may have named ranges too, so try the below, this removes all formulas and named ranges

Sub UnhideSheets()
'Start looping through all worksheets
For Each WS In ThisWorkbook.Worksheets

'makes all sheets visible
Application.ScreenUpdating = False
If WS.Name <> "RandomText" Then
WS.Visible = xlSheetVisible
End If
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next WS


' Deletes all named ranges

Dim NamedRange As Name

For Each NamedRange In ActiveWorkbook.Names
NamedRange.Delete
Next

End Sub

Thanks for your response,

> i am copy pasting only values from macro sheet to ordinary workbook but when i send it to third party some software from there end is detecting the source code for all cells in excel.

Attaching snap shot for reference.

 

[harikris2013]

Thank you its working.