Can I copy and save or paste word docs using query of hyperlinks? 1

[BradVT]

I am trying to copy a lot WORD docs from a directory and paste them to another new directory.

The are about 60,000 existing word docs.

I have an Access 2003 query with 28,545 records that are amoung the 60,000. This query has a Hyperlink column. For example.

W:\POLICY_DOCS\9168_FPE101_General_Change_Exclusion 30_20050502.doc

Is it possible with VBA, to step through this query and some how find the word doc, copy that word doc and save it to some other directory, and then loop to the next in the query?

Thanks for any help.

 

[fumei]

It may be better to post this in the Access forums. I know little about Access.

However, I am pretty sure this is possible. If the hyperlink data has:

W:\POLICY_DOCS\9168_FPE101_General_Change_Exclusion 30_20050502.doc

Then this is, ususally, Hyperlink.Address. In any case, I am sure this can be extracted as a string.

And, as a string, it can be used with FileSystemObject to do the file actions you want to do.

Gerry

 

[MajP]

Fumei,
I thought the same thing, but ran quickly into a snag. Maybe your or someone else can answer. Although you can get the full path as demoed

  destinationPath = "C:\"
  strsql = "select fldHyperlink from tblOne"
  Set rs = CurrentDb.OpenRecordset(strsql, dbOpenForwardOnly)
  Do While Not rs.EOF
    If Not IsNull(rs!fldHyperlink) Then
      Debug.Print Application.HyperlinkPart(rs!fldHyperlink, acFullAddress)
    End If
    rs.MoveNext
  Loop
End Sub

The path is relative, based on location of the current database.

So if the file is a sub folder from the current database folder the path looks like:
To%20read\ESI%20Phones%20-%20User's%20Manual.pdf
which is the current database path & \To read\ESI Phones Users's Manual.pdf

Or if it is in some higher directory:
..\..\..\JetComp.doc
which signifies it is three folders up from the current database folder.

I do not know if the fileSystemObject has any functions that handle a relative path. Does it? I guess you can calculate the absolute path from the relative path and the currentdb path. Does anyone have a function to do that? I was to lazy to try to write it myself, and figured there is an available solution.

So my question is given a relative path, and the absolute current directory path, can you return the absolute path? Thanks.

 

[Gruuuu]

You don't need the Hyperlink.Address, if you can just get the string data from the field?
At that point, the old VB function Name will work

Name "StringFromField" As "NewDirectory\"&"FilenamePart.EXT"

How you get the filename part will be the trickiest.
You could do...

Name "StringFromField" As "NewDirectory\"& _
     MID("StringFromField",InStrRev("StringFromField","\"))

 

[MajP]

Gruuu,
I believe you missed the point. If you could get the absolute path this would be simple. The problem AFAIK is that path information is contained as either a relative path or absolute path. If it is stored as relative there there is no drive letter path (absolute) stored. So the document C:\form.txt is stored as
..\..\..\form.txt (on my computer)
There is no property of the hyperlink field to get C:\form.txt

A hyperlink field is composed of three parts.
1. The display text
2. the path
3. Any references

You can pull a lot of properties off of the field, but AKAIK there is no absolute path (drive letter path).

Now there is a lot of code to get UNC from absolute, but I can not find a solution to get absolute from UNC.

http://msdn.microsoft.com/en-us/library/aa195929(office.11).aspx

The HyperlinkAddress property can contain an absolute or a relative path to a target document. An absolute path is a fully qualified URL or UNC path to a document. A relative path is a path related to the base path specified in the Hyperlink Base setting in the DatabaseNameProperties dialog box (available by clicking Database Properties on the File menu) or to the current database path. If Microsoft Access can't resolve the HyperlinkAddress property setting to a valid URL or UNC path, it will assume you've specified a path relative to the base path contained in the Hyperlink Base setting or the current database path

Depending on how the user loaded their hyperlinks they could have stored absolute paths, but the default functionality is a relative path. To see this right click on the field and select edit hyperlink. The file browser comes up and stores it as a relative path. If you type it in manually it stores it as an absolute path. If the user says that everything is stored as an absolute path this is an easy problem. If not I think you need a function to get absolute from relative.

 

[Gruuuu]

Sorry, I took his example to mean that he did, indeed, have an absolute path.

Ok, you can get the DB path from CurrentDb.Properties.Item(0), but you would definitely have to homebrew a function to convert the relative path, as MajP suggests

 

[fumei]

And if you do - write the function to convert the relative path - for all our sanity...POST it!

And good luck.

Gerry

 

[MajP]

Gruuu,
Actually the OP may have all the hyperlinks saved as absolute paths, and then the answer would be kind of easy. I do know that it is possible to have both. And if you use the native functionality to load a hyperlink you get the relative path. I guess to check you could see if the first three characters are like A:\.
Although the display text can be anything, if it is
W:\POLICY_DOCS\9168_FPE101_General_Change_Exclusion 30_20050502.doc
Then it is also likely that OPs addresses are an absolute path, and we can proceed.

 

[MajP]

OK, I may be the only one interested in this, but I finally found the code to go from relative to absolute. Here is a demo that handles both a relative or absolute path and moves the files. Of course the next step is to update the hyperlink.

Public Sub copyFromHyperlink()
  Dim rs As DAO.Recordset
  Dim strsql As String
  Dim docPath As String
  Dim destinationPath As String
  Dim filePath As String
  Dim fileName As String
  Dim startPath As String
  
  startPath = CurrentDb.Name
  'remove the file name
  startPath = Replace(startPath, getFileName(startPath), "")
  Debug.Print startPath
  destinationPath = "C:\"
  
  strsql = "select fldHyperlink from tblOne"
  Set rs = CurrentDb.OpenRecordset(strsql, dbOpenForwardOnly)
  Do While Not rs.EOF
    If Not IsNull(rs!fldHyperlink) Then
      filePath = Application.HyperlinkPart(rs!fldHyperlink, acFullAddress)
      If isRelativePath(filePath) Then
        filePath = AbsFromRelativePath(startPath, filePath)
        
      End If
      fileName = getFileName(filePath)
      FileCopy filePath, destinationPath & fileName
      Kill filePath
    End If
    rs.MoveNext
  Loop
End Sub

Private Function AbsFromRelativePath(startPath As String _
, relativePath As String) As String

Dim sReturnPath As String
Dim sRelativePath As String
Dim lDirPos As Long
Dim lPathPos As Long

sReturnPath = startPath

If Right$(sReturnPath, 1) = "\" Then 'dump the back slash if there is one
sReturnPath = Left$(sReturnPath, Len(sReturnPath) - 1)
End If

sRelativePath = relativePath

If Left$(sRelativePath, 2) = ".\" Then 'Current folder
sRelativePath = Mid$(sRelativePath, 3)
Else
Do
lDirPos = InStr(1, sRelativePath, "..\")
If lDirPos > 0 Then
sRelativePath = Mid$(sRelativePath, lDirPos + 3)
lPathPos = InStrRev(sReturnPath, "\")
If lPathPos > 0 Then
sReturnPath = Left$(sReturnPath, lPathPos - 1)
Else
Err.Raise 76 'Path not found
End If
End If
Loop While lDirPos > 0
End If

AbsFromRelativePath = sReturnPath & "\" & sRelativePath

End Function
'===================
Public Function isRelativePath(filePath As String) As Boolean
 Dim ltr As String
 Dim slshCln As String
 ltr = UCase(Left(filePath, 1))
 slshCln = Mid(filePath, 2, 2)
 If Not (Asc(ltr) > 64 And Asc(ltr) < 91 And slshCln = ":\") Then
   isRelativePath = True
 End If
End Function

Public Function getFileName(filePath As String) As String
    Dim sPath As String
    Dim sList() As String
    Dim sAns As String
    Dim iArrayLen As Integer
    If Len(filePath) = 0 Then Exit Function
    sList = Split(filePath, "\")
    iArrayLen = UBound(sList)
    If iArrayLen = 0 Then
      sAns = ""
    Else
      sAns = sList(iArrayLen)
    End If
    getFileName = sAns
End Function

 

[BradVT]

Thanks to everybody for the response. I apologize for not participating in my question - I was draged (kicking and screaming) to another project.

Now that I am back, MajP hit it dead right with the last post using his routine with FileCopy filePath, destinationPath & fileName


Thanks again.