Query External File Links

Johnyeates · Post by **Johnyeates** » 22 Feb 2011 17:16

Hi, I have had a look through the How To's and Knowledge base but can find no hint on how to Query the External File Links to check for Media wrongly addressed.
I sometimes correct wrongly spelt/formatted media file names and forget to correct it with the 'Work with External File Links'. I there a Query that I can Download/write that will check for files that have Crosses on them?
My only way to check is to list through all the Media files, very boring.
John

ID:5011

gerrynuk · Post by **gerrynuk** » 22 Feb 2011 22:34

I think there is a wish list item to view only faulty/broken links rather than having to scroll through all of them.

Johnyeates · Post by **Johnyeates** » 23 Feb 2011 11:36

I have tried to find the item on the wish list without any joy. Would you let me know the item heading?
John

Post by **nsw** » 23 Feb 2011 15:18

I don't think it has made as far as the wish list yet. It is in the wish list requests here:

Topic: Work With External File Links

Post by **tatewise** » 25 Feb 2011 19:47

There is no Query that will list faulty/broken links, but here is a technique using Excel.

Open a new Excel spreadsheet and create a VBA function as follows.
To open the VBA editor, hold down the Alt key and press the F11 key, which works in all versions of Excel.
In the VBA editor window use Insert > Module and copy the following function into the main pane.

Function FileExists(File As String)
FileExists = Dir('C:...Family Historian ProjectsProjNameProjName.fh_data' & File) ''
End Function

where 'C:...Family Historian ProjectsProjNameProjName.fh_data' must be changed to the full path for your FH Media folder.
Then use File > Save and close the VBA editor window.

Back in the normal Excel spreadsheet window enter =FileExists(C1) into cell A1, which invokes the VBA function inserted above.

Use FH to create a Query of type Multimedia with Columns:

Heading Expression
Media %OBJE%
File %OBJE._FILE%

Run the Query and copy the entire Result Set to columns B and C of the spreadsheet.
Select cell A1 and copy the =FileExists(Cn) formula down column A for every row that contains Query data.
Then column A should say TRUE whenever the File in column C exists, or FALSE if it does not.

If you Sort the whole spreadsheet then all the FALSE rows will rise to the top, and you can inspect and correct the File references.

Post by **tatewise** » 02 Mar 2011 15:12

The technique described above is now available from the FHUG Downloads at:
http://www.fhug.org.uk/wiki/doku.php?id ... afilelinks
This provides the simple Multimedia Query and a semi-automated spreadsheet for both MS Office Excel and OpenOffice Calc.
It currently caters for about 2,000 media files, but can easily be extended, by copying the expression in column A into more rows..

George167 · Post by **George167** » 29 Mar 2011 16:22

I have just looked in the Downloads for the file http://www.fhug.org.ukwikidoku.php?id=f ... s:contents:
multimediafilelinks without success. Can you tell me please where I can find this please? (see Tatewise 02/03/11)

Post by **tatewise** » 29 Mar 2011 16:35

Either simply click on the orange URL link above, or go to the FHUG Main Menu top left, click Knowledge Base, and enter Multimedia File Links into the Search, and follow the link to Query & Spreadsheet: Multimedia File Links.

George167 · Post by **George167** » 30 Mar 2011 12:08

Dear Tatewise,
Thank you for your reply. Can help me further please? Your download mutimediafilelinks.fhq was downloaded into OpenOffice.org Calc. The result is a read only file, which will not accept the query result set?
Please advise.
Thank you.

Post by **tatewise** » 31 Mar 2011 16:48

I suspect you mean the mutimediafilelinks.ods file for OpenOffice?

I further suspect you have not Extracted the files from the mutimediafilelinks.zip download.

You will need to right-click on this Zip file and use Extract All or use a utility such as WinZip or 7-Zip.

George167 · Post by **George167** » 01 Apr 2011 11:46

Dear Tatewise,
Thank you for your instructions. Everything is fine now.

Family Historian User Group