Solved: Auto updating Hyperlink field

[Adonaioc]

I have a folder full of files

Example file names

C 1-10 1-12 1-14.a.b.dwg
C 1-10 1-12 1-16.b.a.dwg

when one of those files gets updated the revision letter gets bumped up.

C 1-10 1-12 1-14.b.b.dwg
C 1-10 1-12 1-16.c.a.dwg

I have a database with hyperlinks to these files. I want my hyperlink to Auto-update if the file gets revised. I tried to just leave off the last part but access wont open the file then. I thought that somehow the link could run from a-z and get the highest rev letter availible. is this possible? or is it somehow possible to put a variable in the hyperlink, because only 1 revision will be availible at a time. also some files have almost identical name that could cause a problem if a variable is possible

Example:

C 1-10 1-12.a.a.dwg
C 1-10 1-12 1-18.a.a.dwg

A variable in the first link would have to be sure not to return the second one. I hope that makes sence.
Thanks

[CreganTur]

What's your process for updating these drawings? Is it manual, or is it automated somewhere else in your database?

If it's done via your database, then we can have the same code that updates the version number update the table's hyperlink.

If it's a manual process, then we need to know if there's any way to match the file's name to its record in the table. Would it work to compare the Left x characters of the table to the files in the folder and get a valid match every time to the correct file?

One way of implementing this could invlove a loop that goes through each record in your table to see if the hyperlink has a matching file and then if no match is found it would activate the code to find the next versioned file and update the link.

[Adonaioc]

It is manual. a customer calls and changes there requirements and then the file gets changed.


"If it's a manual process, then we need to know if there's any way to match the file's name to its record in the table. Would it work to compare the Left x characters of the table to the files in the folder and get a valid match every time to the correct file?

One way of implementing this could invlove a loop that goes through each record in your table to see if the hyperlink has a matching file and then if no match is found it would activate the code to find the next versioned file and update the link."
Im not exactly sure what you mean. the part of the filename to the left of C 1-10 2-12 1-14.a.a.dwg will always be the same. is the letters between the dots that will change. that file is unique to one entry in the table, C 1-10 2-12 1-14.a.a.dwg would be the C 1-10 2-12 1-14 record.

does that answer your question?

[CreganTur]

I haven't perfected an answer to your problem yet, but I have made some progress. See the attached example database. (To test it you will need to create files to match the hyperlinks in the table).

I used mdmackillop's kb entry (http://vbaexpress.com/kb/getarticle.php?kb_id=245&PHPSESSID=d9a4b5608973df8efeca6f088cfb358c) on looping through files and folders to loop through the files in a folder and compare them against a DAO recordset of your hyperlink table. You cannot Index hyperlinks, so I am running all of this off of the primary key index, which is the record number (for you filenames it's everything before the first period).

This version of the code replaces all of the hyperlinks- it doesn't look to see if the hyperlink doesn't exist.

this should give you a good starting point- I've got some job stuff I need to work on. If you get stuck I'll give what assistance I can!

Private Sub btnUpdateLinks_Click()
Const MyPath = "C:\test2" ' Set the path.
Const FileType = "*.txt"
ProcessFiles MyPath, FileType
End Sub

Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinks", dbOpenTable)

rst.Index = "PrimaryKey"

'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop

'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
rst.Seek "=", Left(strFileName, InStr(1, strFileName, ".") - 1)
rst.Edit
rst.Fields("Link") = strFolder & "\" & strFileName
rst.Update
strFileName = Dir$()
Loop

'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub

[Adonaioc]

this may be a stupid question but I cannot find where you have attached a sample database. do you mean the code you posted?

[CreganTur]

this may be a stupid question but I cannot find where you have attached a sample database. do you mean the code you posted?

Must have forgotten to upload it:whistle:

[Adonaioc]

That is looking pretty good, one issue that I have not been able to work out is that when it populates the link field it is not a clickable link. its just display text

[CreganTur]

That is looking pretty good, one issue that I have not been able to work out is that when it populates the link field it is not a clickable link. its just display text

Yeah- sorry should have mentioned that. I had the same issue while developing that example db.

So far I can't find any reason why that's happening- or a way to make the hyperlinks work again.

Maybe someone else knows why it's not working?


NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Nevermind- I figured it out!

When the code pushes the new name into the field, the table is reading it as the hyperlink's title- the text that should be displayed (Hyperlinks have multiple parameters- Title & Address are the first two- they are separated by a "#").

Take a look at the code below- it solves the issue by adding a "#" and then the file's address again- this completes both parameters to make it into a valid hyperlink.

Do Until strFileName = ""
rst.Seek "=", Left(strFileName, InStr(1, strFileName, ".") - 1)
rst.Edit
rst.Fields("Link") = strFolder & "\" & strFileName & "#" _
& strFolder & "\" & strFileName
rst.Update
strFileName = Dir$()
Loop

[Adonaioc]

You are a genius sir! Thank you very much for your help. Ill mark this one solved but im sure ill hit another road bump soon.

[Adonaioc]

One more thing, the folder That I Have has other files in it that are not in the database and I keep getting an error, because it doesnt know what to do when it doesnt match. Is there a way to tell it to skip a file its not referenced in the database?

[Adonaioc]

I added the on error line is that going to screw anything up? Is it even in the right place?


'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""

On Error Resume Next
rst.Seek "=", Left(strFileName, InStr(1, strFileName, ".") - 1)
rst.Edit
rst.Fields("Link") = strFolder & "\" & strFileName & "#" _
& strFolder & "\" & strFileName
rst.Update
strFileName = Dir$()
Loop

[CreganTur]

It's best practice to put the On Error command near the start of your code, right below where you declare all of your variables.

It won't screw anything up... as long as you're handling your errors correctly ;)