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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

eg

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

eg

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

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

I missed out your question regards

Please Login or Register  to view this content.

In XL

Dates are integers... ie 1 = 1st Jan 1900, 2 = 2nd Jan 1900 etc..., today (14th Feb 09) = 39858
(on 1900 Date System)

Times are decimal... given a day represents 1 it follows that noon represents 0.5 (1/2 a day)... as 6 am represents 0.25 (1/4 a day) and 6pm represents 0.75 (3/4 a day)

So given the above it follows that you can add/subtract with dates & times quite easily...
In your case we needed to create a file name based on Date in A2 & Time in B2 such that the Date is converted to mm-dd-yy format and the time to h mm format... now we could do this using 2 TEXT functions:

Please Login or Register  to view this content.

However it would make just as much sense to add the date & time together and do one string conversion:

Please Login or Register  to view this content.

which in your case translates to:

Please Login or Register  to view this content.

**shg** · 02-14-2009, 01:38 PM

binar,

Please post links to the threads in other forums where you have posted the same question.

**binar** · 02-14-2009, 01:44 PM

DonkeyOte,
I really appreciate you summerizing to me the intricacies relating to your formula. It's a very impressive formula ! Thanks a lot for all of your help. I finally arrived at a formula that works beutifully:

=HYPERLINK("C:\Root\4x\trades\2009 forward\01 USD-CAD\"&TEXT($A14,"mm-dd-yy")&" "&TEXT($B14,"hh mm")&".pdf",TEXT($A14,"mm-dd-yy")&" "&TEXT($B14,"hh mm")&".pdf")

This formula hides the entire path and only shows the filename. Very Cool! Since I have 24 different currency pairs, I need to have 24 different versions of this formula since each one is embedded with a unique path. From this point all that is required is a filldown operation across 24 columns and viola! All of my future autamatically generated PDF screencaptures will be automatically hyperlinked. Very Very COOL! Thanks again to everyone for their help and I'm closing this thread as SOLVED

Originally Posted by DonkeyOte

I missed out your question regards

Please Login or Register  to view this content.

In XL

Dates are integers... ie 1 = 1st Jan 1900, 2 = 2nd Jan 1900 etc..., today (14th Feb 09) = 39858
(on 1900 Date System)

Times are decimal... given a day represents 1 it follows that noon represents 0.5 (1/2 a day)... as 6 am represents 0.25 (1/4 a day) and 6pm represents 0.75 (3/4 a day)

So given the above it follows that you can add/subtract with dates & times quite easily...
In your case we needed to create a file name based on Date in A2 & Time in B2 such that the Date is converted to mm-dd-yy format and the time to h mm format... now we could do this using 2 TEXT functions:

Please Login or Register  to view this content.

However it would make just as much sense to add the date & time together and do one string conversion:

Please Login or Register  to view this content.

which in your case translates to:

Please Login or Register  to view this content.

**binar** · 02-14-2009, 01:50 PM

Sorry Shg,
Wasn't aware this was required:

http://www.ozgrid.com/forum/showthre...492711#Similar

Originally Posted by shg

binar,

Please post links to the threads in other forums where you have posted the same question.

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

LinkBack

Thread Tools

Rate This Thread

Display

Create Hyperlinks From PDF Filenames Then Match Filenames To Rows & Columns

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions