Macro for drill down & hyperlink automation

B

binar

Fellow Forum Members,
I'm using Snagit 9 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

I have setup an Excel Matrix 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 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.

Is a script with this amount of automation possible with the VBA programming
language? My dream is to be able to run such a script and then have my Excel
Matrix automatically updated with hyperlinks to the newest PDF files that
have been added since I last ran the script. A script like this will
eliminate a lot of time and human errors associated to doing it manually.

Moreover, If someone knows of an ADDIN that I could add to my Excel 2007
that would enable me to perform such a task please let me know.

Any help will be greatly appreciated. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top