Hyperlink to latest file in the directory. File having last 8 characters as date yyyymmdd

[Vijay Kumar]

Situation

1. I have a master quality control document in excel.
2. That quality control document refers to quality process documents (.pdf files).
3. The .pdf files are names as under:
a. First 6 characters (like 755-05) then "-" and then the revision date 20130512(8 characters)
b. the full file name would be 755-05-20130512.pdf
4. If I link the above file in excel file referred in point 1. above, no issues.
5. However, when I change something in that document and create a new revision (for example, I changed it today), I will name the file 755-05-20130913.pdf.
6. currently, my only option is to go to excel spreadsheet in point n0. 1 and change the link.
7. I do not want to do that as every time I have to remember to change the link. I want link to change the link DYNAMICALLY to the latest file based on the first 6 characters of the file name. IS THIS POSSIBLE? Please help!!

 

[NARAYANK991]

Hi Vijay Kumar ,

This is possible with VBA , and you can make it easier if you store the revision number separately in a cell , so that rather than change the date portion within the filename in the worksheet , only the cell contents can be changed.

You can make it easier to code the macro if you upload at least a sample sheet so that the cell / column references can be put into the code.

Narayan

 

[SirJB7]

Hi, Vijay Kumar!

I agree with what NARAYANK991 posted that with VBA it'd be easier. But that's not all, it's just the top of the iceberg. Whether or not you separte the prefix name from the suffix date won't be relevant.

If I haven't understood wrong, you have a folder with an Excel workbook and a lot of .pdf files. In a worksheet of that workbook you have a column for which it cell contents point to each of those .pdf.
Now, as you can't sit easily on the top of the hill and rest, you want to edit one of the .pdf saving it with the actual (or a different date) as part of the filename, and you want that the cell that pointed to the original date file get updated with the new date file.

AFAIK you have 2 choices, that will be appropriate according to the use of the workbook:
a) If the workbook is used sporadically in relation with the .pdf updates, you can perform a search at open time within the folder to retrieve the last updated document for each prefix and update the cells content.
b) If that's not the case, the only situation that I can imagine to be safe is to place a mark on the next column of the document to be updated an manually by a command button or automatically via the worksheet change event, run a procedure that calls Adobe Acrobat XI (or whatever you use to update the .pdf files) and set the new entry to the document name using the actual date. The only risk is that you make a mistake when saving as the .pdf with the new name.

Check both alternatives and tell us you comments.

Regards!

 

[Sam Mathai Chacko]

Have a look at this one

Function GETLATESTFILE(strPath As String, strInitialFileName As String, strFileExtension As String) As String

    Dim strFile As String
    Dim lngDate As Long
    strFile = Dir(strPath & strInitialFileName & "*" & strFileExtension)
    While strFile <> ""
        lngDate = Application.Max(lngDate, CLng(Replace(Replace(strFile, strInitialFileName, ""), strFileExtension, "")))
        strFile = Dir
    Wend
    GETLATESTFILE = strPath & strInitialFileName & lngDate & strFileExtension
    
End Function