June 3, 2023 – Hunting External Links
Tonight I used the below vba code posted here by Domenic to successfully generate a list of each cell in a workbook which contained a link to an outside Excel file.
This code is more convenient than searching for .xl in Find with the ‘Look in: Formulas’ setting.
Keep in mind that if you open an Excel workbook and this troubling warning appears:
. . . the source of the problem will not be shown in the first column of the ‘Find All’ search results. Look in the reference column (or the Formula column in the Find tool) for a reference to a different file embedded in a formula. See: prices.xlsx in this example for a search run in the inventory.xlsx workbook:
Option Explicit
Sub ListLinks()
Dim Wks As Worksheet
Dim rFormulas As Range
Dim rCell As Range
Dim aLinks() As String
Dim Cnt As Long
If ActiveWorkbook Is Nothing Then Exit Sub
Cnt = 0
For Each Wks In Worksheets
On Error Resume Next
Set rFormulas = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulas Is Nothing Then
For Each rCell In rFormulas
If InStr(1, rCell.Formula, “[“) > 0 Then
Cnt = Cnt + 1
ReDim Preserve aLinks(1 To 2, 1 To Cnt)
aLinks(1, Cnt) = rCell.Address(, , , True)
aLinks(2, Cnt) = “‘” & rCell.Formula
End If
Next rCell
End If
Next Wks
If Cnt > 0 Then
Worksheets.Add before:=Worksheets(1)
Range(“A1”).Resize(, 2).Value = Array(“Location”, “Reference”)
Range(“A2”).Resize(UBound(aLinks, 2), UBound(aLinks, 1)).Value = Application.Transpose(aLinks)
Columns(“A:B”).AutoFit
Else
MsgBox “No links were found within the active workbook.”, vbInformation
End If
End Sub
June 12, 2023 – Trans-Atlantic Data Privacy Framework Approval in Doubt
The EU-US Privacy shield has been invalid since 2020, when the European Court of Justice invalidated it in Data Protection Commissioner v Facebook Ireland and Maximillian Schrems), a follow-up decision to a 2015 ruling in the first case filed by Schrems against Facebook which found the Safe Harbour Privacy Principles did not offer sufficient protections for the personal data of EU citizens because of American surveillance programs.
President Biden issued an Executive Order in October 2022 approving the Trans-Atlantic Data Privacy Framework. The DPF has not yet been approved by the European Commission, the executive body of the EU.
The DPF will require American intelligence agencies to update their regulations to comply with new safeguards for protecting data privacy. A Civil Liberties Protection Officer in the Office of the Director of National Intelligence would conduct investigations into complaints about violations of data privacy. The Attorney General would create a Data Protection Review Court to independently review the Civil Liberties Protection Officer’s holdings. A Privacy and Civil Liberties Oversight Board would also conduct an annual review to ensure intelligence agencies comply with the order to update their policies.
The Civil Liberties Committee of the European Parliament issued this press release this April faulting the Executive Order for not going far enough to safeguard personal data. The MEPs noted the decisions of the Data Protection Review Court wouldn’t be publicly disclosed and could be overturned by the President. They question whether or not the DPF would be held up in an EU court. A resolution passed by the Committee recommending that the European Commission not approve the DPF got 37 votes in favor, 0 against, and 21 abstentions. The European Commission can approve the DPF without the consent of the EU Parliament.
June 18, 2023 – Remove All Images From a Webpage
You can remove all of the images displayed on any web page by making use of the ‘Remove Images’ bookmarklet posted here: https://www.hongkiat.com/blog/100-useful-bookmarklets-for-better-productivity-ultimate-list/
A bookmarklet is javascript code that you can add as a bookmark on your web browser. On the Hongkiat site simply select the link for the bookmarklet and drag it to your bookmarks bar. When you are on a web page and you click the link, the images will no longer be displayed.
June 23, 2023 – Getting Rid of Those Pesky .db Files
The Litigation Support Tip of the Night for February 7, 2017 described how to remove thumbnail image files which could not be deleted through the normal right click process. Lately I have found when working in Windows 11 that the technique of deleting the files in the ‘Content’ view no longer works. I get an error message like this:
This week I was able to successfully implement the solution posted here by Anand Khanse which recommends a change in the Registry Editor. Browse to HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows and right click and select New . . . Key
Create a new folder named, ‘Explorer’ and then right click on it and again select New
. . . select the option for ‘DWORD (32-bit) Value’, and name the DWORD as DisableThumbsDBOnNetworkFolders
If you double-click on this file a dialog box will open which allow you to set the value data to 1
When I made this change in RegEdit I was able to successfully delete thumbnail .db file after I rebooted Windows.
As always exercise great caution when making changes in Registry Editor.
You can get the images for a web page back by refreshing the page. The bookmarklet may not work with animated images.
June 30, 2023 – Javascript to Split PDF Into 5 Page Segments and Save & Rename
You can use javascript code in Adobe Acrobat to extract set ranges of pages from a PDF file and then save and rename each extracted PDF range as a new file.
A user named vvb posted the following code here:
/* Extract Pages to Folder */var re = /.*\/|\.pdf$/ig; var filename = this.path.replace(re,””); var lastPage=this.numPages-1; { for ( var i = 0; i < this.numPages; i = i + 2 ) this.extractPages ({ nStart: i, nEnd: i + 1, cPath : filename + “_page_” + (i+1) + “.pdf” }); };
This code can be inserted in Acrobat and edited so that it takes 5 pages at a time from a source PDF file and then names them sequentially with a prefix.
In Acrobat go to More Tools and select Action Wizard. On the top toolbar select the option for ‘New Action’. In the ‘More Tools’ menu select the ‘Execute Javascript’ option – doubleclick on it so it appears in the right pane.
Uncheck the ‘Prompt User’ option, and then click on ‘Specify Settings’.
Edit the code so that the line beginning, ” for ( var i = 0; i < this.numPages; i = i + ” specifies how many pages you want each PDF to be., and the line beginning “nEnd: i + ” ends with one number less. Modify the line beginning, ” cPath : ” so that it has the letter prefix for each file. The script will name each extracted file with the page number from the original file that the excerpt begins with.
/* Extract Pages to Folder */var re = /.*\/|\.pdf$/ig;
var filename = this.path.replace(re,””);
var lastPage=this.numPages-1;
{
for ( var i = 0; i < this.numPages; i = i + 5 )
this.extractPages
({
nStart: i,
nEnd: i + 4,
cPath : “ACME” + (i+1) + “.pdf”
});
};
Save and name the action.
Click on the action in the Actions List and then select the files that you want to run it on. Click the ‘Start’ button.
The script will create a new file (in the same folder as the source file(s)) named with the prefix you enter in the code and the page number on which the excerpt begins.
*The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.
