Excel 2007 : Create Hyperlinks From PDF Filenames Then Match Filenames To Rows & Columns

**binar** · 02-12-2009, 11:00 PM

Fellow Forum Members,
I'm using Snagit 9 (a screen capture app) to automatically generate and feed PDF screen capture files every 30 minutes into each of the folders listed below. For one daily cycle I have a total of 1152 (48 x 24 currency pairs = 1152) new screen captures nested in the folders shown below:

C:\Root\4x\trades\2009 forward\01 USD-CAD
C:\Root\4x\trades\2009 forward\02 USD-JPY
C:\Root\4x\trades\2009 forward\03 USD-CHF
C:\Root\4x\trades\2009 forward\04 GBP-USD
C:\Root\4x\trades\2009 forward\05 GBP-CAD
C:\Root\4x\trades\2009 forward\06 GBP-CHF
C:\Root\4x\trades\2009 forward\08 GBP-NZD
C:\Root\4x\trades\2009 forward\09 CHF-JPY
C:\Root\4x\trades\2009 forward\10 EUR-USD
C:\Root\4x\trades\2009 forward\11 EUR-CAD
C:\Root\4x\trades\2009 forward\12 EUR-GBP
C:\Root\4x\trades\2009 forward\13 EUR-CHF
C:\Root\4x\trades\2009 forward\14 EUR-JPY
C:\Root\4x\trades\2009 forward\15 EUR-AUD
C:\Root\4x\trades\2009 forward\17 AUD-NZD
C:\Root\4x\trades\2009 forward\18 AUD-CAD
C:\Root\4x\trades\2009 forward\19 AUD-USD
C:\Root\4x\trades\2009 forward\20 AUD-CHF
C:\Root\4x\trades\2009 forward\21 AUD-JPY
C:\Root\4x\trades\2009 forward\22 NZD-JPY
C:\Root\4x\trades\2009 forward\23 NZD-USD
C:\Root\4x\trades\2009 forward\24 NZD-CHF

Attached is an Excel matrix I have setup to help me track all these screen captures. "ROW 1" lists all of the Currency pairs and "Column A" and "Column B" contain the date and time in military format.

When Snagit generates a screen capture it assigns an automatically generated filename in this format:

"USD-CAD 02-11-09 15 00.pdf " (prefix field / system date field / and 15 00 is military time field for 3:00 PM).

I’m not a VBA programmer, therefore I would be very grateful if someone can develop for me a script that will do the following:

1. Drill down into each of the directories shown above and match the correct PDF file to the correct cell location in my Excel Matrix by using the data in PDF file name itself. The script will need to be able to recognize the prefix portion (NZD-USD) of the PDF filename to find the correct column in the Excel Matrix followed by identifying both the date and time portions of the filename to find the correct row in the Excel Matrix.

2. Once the VBA script finds the correct cell in the Excel Matrix it then automatically generates a hyperlink to the correct PDF file that belongs in that particular cell location.

Can a VBA script accomplish such a task? How will such a VBA script know not to re-hyperlink PDF files that already have been hyperlinked in the Excel Matrix? What VBA programing approach needs to be used so that the data making up the PDF filename is utilized by the VBA script to locate the correct cell and then perform an automatic hyperlink between the cell and the PDF file?

Any help will be greatly appreciated. Thanks.

**DonkeyOte** · 02-13-2009, 04:03 AM

To clarify...

1) 01 USD-CAD is a folder ?
ie
the matrix shows filename as:
USD-CAD mm-dd-yy [hh] mm.pdf

your sheet shows filepath as:
C:\Root\4x\trades\2009 forward\01 USD-CAD
C:\Root\4x\trades\2009 forward\02 USD-JPY
etc...

so for say a USD-CAD.pdf on 12th Feb at 2pm would the below be the correct link ?

C:\Root\4x\trades\2009 forward\01 USD-CAD\USD-CAD 02-12-09 14 00.pdf

2) Will there always be a file for any thing listed in the matrix ?

I ask because I think you don't need to iterate your folders to create the links merely the cells themselves... you can create the link using the date/time, col header & cell value to generate the appropriate link.

**DonkeyOte** · 02-13-2009, 04:09 AM

Also please do not post duplicates.

http://www.excelforum.com/excel-prog...utomation.html

Mods, can either the above be closed or this thread and my above post be moved to the other ?
(the other being programming may be the more suitable forum)

**DonkeyOte** · 02-13-2009, 05:32 AM

In hindsight.. have I misinterpreted... are you in fact trying to populate the matrix -- ie the records you show in C, E etc do not exist ... you are trying to insert them & set a hyperlink simultaneously ?

If so there is the possibility of using a HYPERLINK function - ie a formula ... this will create links based on the Date/Time & Header column ... if the files don't exist, clicking on them will give a "can't open specified file" else they will open the file accordingly.

For ex... the below formula in C2 would generate a link to the USD-CAD pdf file for 11th Feb 2009 midnight file

'Currency Pair!C2:
=IF(OR(COUNT($A2:$B2)<2,C$1=""),"",HYPERLINK($A$1&"\"&TRIM(REPLACE(C$1,1,3,""))&"\"&TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf",TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf"))

Notes:

1. A1 holds: C:\Root\4x\trades\2009 forward

2. The above assumes per earlier post that within 2009 Forward folder there are sub folders for each currency pair (ie folder for USD-CAD, folder for USD-JPY etc...)

3. The above formula can be applied across all rows & columns where links are required.

**binar** · 02-13-2009, 11:18 PM

DonkeyOte,
Thanks a lot for your help. Your approach took me a while to understand. Nevertheless, what I understand is that it's not necessarily necessary to develop a VBA script. Your approach is more elegant. I just populate my matrix with a fill down command and doing this automatically generates a PDF filename that matches the file naming convention used by Snagit 9, hence it's automatically hyperlinked since both match. VERY COOL !!!

The formula below is the one I ended up using:
=HYPERLINK("C:\Root\4x\trades\2009 forward\01 USD-CAD\"&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf")

If I compare it to the one you posted:
=HYPERLINK($A$1&"\"&TRIM(REPLACE(C$1,1,3,""))&"\"&TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf",TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf"))

I can't help but wonder what is going on with the Trim, Replace and Sum command. You lost me here.

I started another thread to address another issue that relates to hiding the path in a hyperlink. I hope you can take a look at it. The name of the thread is "Hyperlink with hidden path & only shows filename?"

Thanks for the help.

Originally Posted by DonkeyOte

In hindsight.. have I misinterpreted... are you in fact trying to populate the matrix -- ie the records you show in C, E etc do not exist ... you are trying to insert them & set a hyperlink simultaneously ?

If so there is the possibility of using a HYPERLINK function - ie a formula ... this will create links based on the Date/Time & Header column ... if the files don't exist, clicking on them will give a "can't open specified file" else they will open the file accordingly.

For ex... the below formula in C2 would generate a link to the USD-CAD pdf file for 11th Feb 2009 midnight file

'Currency Pair!C2:
=IF(OR(COUNT($A2:$B2)<2,C$1=""),"",HYPERLINK($A$1&"\"&TRIM(REPLACE(C$1,1,3,""))&"\"&TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf",TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf"))

Notes:

1. A1 holds: C:\Root\4x\trades\2009 forward

2. The above assumes per earlier post that within 2009 Forward folder there are sub folders for each currency pair (ie folder for USD-CAD, folder for USD-JPY etc...)

3. The above formula can be applied across all rows & columns where links are required.

**DonkeyOte** · 02-14-2009, 04:05 AM

TRIM -- will remove surplus leading & trailing spaces from a string... so " Hello " would become "Hello"... this was used because on your sample file some of your headers in Row 1 had surplus trailing spaces.

The HYPERLINK function has 2 arguments:

HYPERLINK(link location, [friendly name])

The latter argument is optional but is what you want to use to show different text to the actual link address...

eg

HYPERLINK("http://www.google.com","Google")

Would generate a link to the full URL but display "Google" in the cell.

You will notice that in the formula I provided I provided you with the friendly name:

=IF(OR(COUNT($A2:$B2)<2,C$1=""),"",HYPERLINK($A$1&"\"&TRIM(REPLACE(C$1,1,3,""))&"\"&TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf",TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf"))

The REPLACE was used to remove the "01 ", "02 " from the headers in row 1 when creating the string...

=REPLACE(text,start pos,num chars,new text)

eg

=REPLACE("Hello",1,2,"")

would generate: "llo" as 2 characters commencing from position 1 of the string Hello are replaced with nulll... this

=REPLACE("Hello",1,2,"x")

would generate: "xllo", as instead of Nulls ("") we use "x"

Going forward, please don't create new threads if the question you have is related to your current thread - raising the question in the same thread will get a resolution (as demonstrated).

I'm still curious as to your file path being a constant (01 USD-CAD) -- surely the other currencies reside in different folders, no ? The formula I provided would adapt the folder (01 USD-CAD) accordingly so you can apply a common formula across all currency pair columns (ie that provided previously) ... though seemingly the "01 " is important which I removed originally so I would revise it to:

=HYPERLINK($A$1&"\"&TRIM(C$1)&"\"&TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf",TRIM(REPLACE(C$1,1,3,""))&" "&TEXT(SUM($A2:$B2),"MM-DD-YY h mm")&".pdf"))

I'm also confused because your sample implied that the pair was included in the file name eg USD-CAD 02-11-09 0 00.pdf whereas again your latest formula implies this not to be the case - you're just using the Date & Time to create the file name - the pair is ignored (only relevant to the folder perhaps) ?

Regardless, if you're happy that's fine by me.