Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Drill Down and Hyperlink Automation
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). Im 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Drill Down and Hyperlink Automation
On Thu, 12 Feb 2009 14:08:00 -0800, binar
wrote: 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). It seems that the HYPERLINK worksheet function would work well here. If your currency pairs start in C1 and go accross row 1 in the same order you have them listed here, then put this formula in C2 and fill down and to the right as necessary. =HYPERLINK("C:\Root\4x\trades\2009 forward\"&TEXT(COLUMN()-2,"00")&" " &C$1&"\"&C$1&" "&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf") -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Drill Down and Hyperlink Automation
Dick,
Thanks a lot for taking the time to answer my post. I appreciate your help a lot because doing fancy things with Excel is something I am not too good at. I like your idea a lot. It is a very elegant solution that was not anywhere near my radar. I thought developing a complex VBA script was going to be the only solution. I tried it out and it works. I'm going to fill down your formula all the way down to the end of 2009. Since the Snagit PDF file naming convention matches the filename that is generated by your formula the hyperlink works ! I think this is very cool ! However, there is one change I hope you can tell me how to make. Currently, your forumula displays the entire path (starting from C:\Root folder). Is there anything that can be added to your formula that will show only the following data as a hyperlink: 02-11-09 11 30.pdf I would like your formula to hide all of the folders that make up the path. Here is your formula with some changes I made: HYPERLINK("C:\Root\4x\trades\2009 forward\01 USD-CAD\"&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf") Is hiding the entire path possible? Any help will be appreciated. THanks. "Dick Kusleika" wrote: On Thu, 12 Feb 2009 14:08:00 -0800, binar wrote: 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). It seems that the HYPERLINK worksheet function would work well here. If your currency pairs start in C1 and go accross row 1 in the same order you have them listed here, then put this formula in C2 and fill down and to the right as necessary. =HYPERLINK("C:\Root\4x\trades\2009 forward\"&TEXT(COLUMN()-2,"00")&" " &C$1&"\"&C$1&" "&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf") -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Drill Down and Hyperlink Automation
On Fri, 13 Feb 2009 17:34:05 -0800, binar
wrote: there anything that can be added to your formula that will show only the following data as a hyperlink: 02-11-09 11 30.pdf I would like your formula to hide all of the folders that make up the path. Here is your formula with some changes I made: HYPERLINK("C:\Root\4x\trades\2009 forward\01 USD-CAD\"&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf") Is hiding the entire path possible? Any help will be appreciated. THanks. Yep. The second (optional) argument to HYPERLINK is friendly_name. HYPERLINK("C:\Root\4x\trades\2009 forward\01 SD-CAD\"&TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf",TEXT($A2,"mm-dd-yy")&" "&TEXT($B2,"hh mm")&".pdf) -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Drill Down Hyperlink Automation ? | Excel Programming | |||
Data Mining and Hyperlink Automation Macro Help | Excel Discussion (Misc queries) | |||
Macro for drill down & hyperlink automation | Excel Discussion (Misc queries) | |||
Pivot Chart Drill through / Hyperlink to detail report | Excel Programming | |||
Drill-Down Pivot Macro | Excel Programming |