UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Doing An External File Search And Open From A Form, Access 2016    
 
   
ollyhutsy
post Nov 13 2019, 07:26 AM
Post#1



Posts: 64
Joined: 16-October 19
From: United Kingdom


I am trying to open a drawing file from an access form. My problem is the folder and file names are not the same as in the access database. For example the drawing name will be 34947-901-21. I am wanting to open this file location on a button press:N:Drawing Files\34947\901 Conveyor\21 Front Mount Angle Bracket. So i would need to take different parts of the the document name as string eg. "901" and "21" and do a search in the folder and open the first folder/document. Any suggestions on how i would go about doing this?


Private Sub FileOpen_Click()
Dim sBasePath As String
Dim sBaseFileName As String

sBasePath = "N:Drawing Files\34947\901 Conveyor\"
sBaseFileName = "21 Front Mount Angle Bracket"

Application.FollowHyperlink sBasePath & sBaseFileName & ".dft"
End Sub
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 07:43 AM
Post#2


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


It's not clear which version is stored in the db and which is the actual file name/path you are after.

So is the actual file name: 34947-901-21.dft ?

Give a bit more description please

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 08:15 AM
Post#3



Posts: 64
Joined: 16-October 19
From: United Kingdom


21 Front Mount Angle Bracket is the actual file name in this example however this will change. Me.documentName will be what i am using to get my file path however the file name wont be the same. So in this case the document name is "34947-901-21". i am wanting to search for the file name which starts with 21 and open it
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 08:45 AM
Post#4


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


You can use the Dir() function to try and find the folders/files using a wildcard to grab the first match.

This assumes your document number is always in the format xxxxx-xxx-xx (ie 3 numbers separated by 2 dashes)

CODE
Private Sub FileOpen_Click()

  Dim strDoc As String, strPath As String, strFile AS String, arrPathParts AS Variant, i As Integer
  Const BASE_FLDR As String = "N:Drawing Files\"

  strDoc = Me.documentName     ' 34947-901-21
  arrPathParts = Split(strDoc, "-")
  If UBound(arrPathParts) <> 2 Then
    MsgBox "Document Name is not in correct format"
    Exit Sub
  End If
  strPath = BASE_FLDR & arrPathParts(0)   ' N:Drawing Files\34947
' Check  folder exists
  If Dir(strPath, vbDirectory) = arrPathParts(0) Then
'   Look for next folder beginning with the second lot of digits
    strPath = strPath & arrPathParts(1)   ' N:Drawing Files\34947\901
    Debug.Print "Looking for folder like: ", strPath & " *"
    strPath = Dir(strPath & " *", vbDirectory)
'   Check a folder is found
    If Len(strPath) Then
      Debug.Print "Found: ", strPath
'     Search for file
      Debug.Print "Looking for file like: ", strPath & "\" & arrPathParts(2) & "*.dft"
      strFile = Dir(strPath & "\" & arrPathParts(2) & "*.dft")
'     Check file is found
      If Len(strFile) Then
        Debug.Print "Found: ", strFile
        Application.FollowHyperlink strPath& "\" & strFile
      Else
        Debug.Print "File not found"
      End If
    Else
      Debug.Print "Folder not found"
    End If
  Else
    Debug.Print "Folder not found: ", strPath
  End If
      
End Sub

(untested)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 09:06 AM
Post#5



Posts: 64
Joined: 16-October 19
From: United Kingdom


just using the document name : 34947-901-21 as a test for now. Tried to follow your code and understand what is going on for the most part, however nothing is happening on my command click. Not an error and no file opening. any suggestions?


Const BASE_FLDR As String = "N:Drawing Files\"

strDoc = "34947-901-21" ' 34947-901-21
arrPathParts = Split(strDoc, "-")
If UBound(arrPathParts) <> 2 Then
MsgBox "Document Name is not in correct format"
Exit Sub
End If
strPath = BASE_FLDR & arrPathParts(0) ' N:Drawing Files\34947
' Check folder exists
If Dir(strPath, vbDirectory) = arrPathParts(0) Then
' Look for next folder beginning with the second lot of digits
strPath = strPath & arrPathParts(1) ' N:Drawing Files\34947\901
Debug.Print "Looking for folder like: ", strPath & " *"
strPath = Dir(strPath & " *", vbDirectory)
' Check a folder is found
If Len(strPath) Then
Debug.Print "Found: ", strPath
' Search for file
Debug.Print "Looking for file like: ", strPath & "\" & arrPathParts(2) & "*.dft"
strFile = Dir(strPath & "\" & arrPathParts(2) & "*.dft")
' Check file is found
If Len(strFile) Then
Debug.Print "Found: ", strFile

Application.FollowHyperlink strPath & "\" & strFile

Else
Debug.Print "File not found"
End If
Else
Debug.Print "Folder not found"
End If
Else
Debug.Print "Folder not found: ", strPath
End If
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 09:08 AM
Post#6



Posts: 64
Joined: 16-October 19
From: United Kingdom


N:\Drawing Files\34947\901 Conveyor\21 Front Mount Angle Bracket

this is the file path i am eventually trying to open from the document name.
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 09:18 AM
Post#7


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


Is anything printed to the Immediate Window (Ctrl+G) ?

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 09:25 AM
Post#8



Posts: 64
Joined: 16-October 19
From: United Kingdom


Looking for folder like: N:\Drawing Files\34947901 *
Folder not found

Not sure why the "\" is not there
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 09:35 AM
Post#9


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


There is an error in the code here:
CODE
' ...
'   Look for next folder beginning with the second lot of digits
    strPath = strPath & arrPathParts(1)   ' N:Drawing Files\34947\901
' ...

It should read:
CODE
' ...
'   Look for next folder beginning with the second lot of digits
    strPath = strPath & "\" & arrPathParts(1)   ' N:Drawing Files\34947\901
' ...

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 09:35 AM
Post#10



Posts: 64
Joined: 16-October 19
From: United Kingdom


Dim strDoc As String, strPath As String, strFile As String, arrPathParts As Variant, i As Integer, BASE_FLDR As String

BASE_FLDR = "N:\Drawing Files\"
strDoc = "34947-901-21" ' 34947-901-21
arrPathParts = Split(strDoc, "-")
If UBound(arrPathParts) <> 2 Then
MsgBox "Document Name is not in correct format"
Exit Sub
End If
strPath = BASE_FLDR & arrPathParts(0) ' N:Drawing Files\34947
' Check folder exists
If Dir(strPath, vbDirectory) = arrPathParts(0) Then
' Look for next folder beginning with the second lot of digits
strPath = strPath & "\" & arrPathParts(1) ' N:Drawing Files\34947\901
Debug.Print "Looking for folder like: ", strPath & " *"
strPath = Dir(strPath & " *", vbDirectory)
' Check a folder is found
If Len(strPath) Then
Debug.Print "Found: ", strPath
' Search for file
Debug.Print "Looking for file like: ", strPath & "\" & arrPathParts(2) & "*.dft"
strFile = Dir(strPath & "\" & arrPathParts(2) & "*.dft")
' Check file is found
If Len(strFile) Then
Debug.Print "Found: ", strFile

Application.FollowHyperlink strPath & "\" & strFile

Else
Debug.Print "File not found"
End If
Else
Debug.Print "Folder not found"
End If
Else
Debug.Print "Folder not found: ", strPath
End If

Got a tiny bit further now, Looking for file like: 901 Conveyor\21*.dft
File not found

is the new error.
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 09:42 AM
Post#11


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


Yikes! Sorry, I warned you this was untested!!!
CODE
' ...
'   Look for next folder beginning with the second lot of digits
    strPath = BASE_FLDR & "\" & strPath & "\" & arrPathParts(1)   ' N:Drawing Files\34947\901
' ...

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 09:53 AM
Post#12



Posts: 64
Joined: 16-October 19
From: United Kingdom


Dim strDoc As String, strPath As String, strFile As String, arrPathParts As Variant, i As Integer
Const BASE_FLDR As String = "N:Drawing Files\"

strDoc = "34947-901-21" ' 34947-901-21
arrPathParts = Split(strDoc, "-")
If UBound(arrPathParts) <> 2 Then
MsgBox "Document Name is not in correct format"
Exit Sub
End If
strPath = BASE_FLDR & arrPathParts(0) ' N:Drawing Files\34947
' Check folder exists
If Dir(strPath, vbDirectory) = arrPathParts(0) Then
' Look for next folder beginning with the second lot of digits
strPath = BASE_FLDR & "\" & strPath & "\" & arrPathParts(1) ' N:Drawing Files\34947\901
Debug.Print "Looking for folder like: ", strPath & " *"
strPath = Dir(strPath & " *", vbDirectory)
' Check a folder is found
If Len(strPath) Then
Debug.Print "Found: ", strPath
' Search for file
Debug.Print "Looking for file like: ", strPath & "\" & arrPathParts(2) & "*.dft"
strFile = Dir(strPath & "\" & arrPathParts(2) & "*.dft")
' Check file is found
If Len(strFile) Then
Debug.Print "Found: ", strFile

Application.FollowHyperlink strPath & "\" & strFile

Else
Debug.Print "File not found"
End If
Else
Debug.Print "Folder not found"
End If
Else
Debug.Print "Folder not found: ", strPath
End If


: strPath : "N:Drawing Files\\N:Drawing Files\34947\901" :

'Bad file name'

is this because of the line strPath = BASE_FLDR & arrPathParts(0) and

strPath = BASE_FLDR & "\" & strPath & "\" & arrPathParts(1)

so the base folder is repeating?



Go to the top of the page
 
ollyhutsy
post Nov 13 2019, 10:53 AM
Post#13



Posts: 64
Joined: 16-October 19
From: United Kingdom


sorted it now, Thanks a lot for the help!!
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 10:56 AM
Post#14


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


OK,

I thought I had better test this!!! blush.gif

I put all this code in to a separate function which you can just call from the click event.

Paste into a standard module:
CODE
Function OpenFile(strDoc As String) As Boolean

  Dim blRet As Boolean, _
      strPath As String, strFolder As String, strFile As String, _
      arrPathParts As Variant, i As Integer
      
  Const BASE_FLDR As String = "N:Drawing Files\"
  
  arrPathParts = Split(strDoc, "-")
  If UBound(arrPathParts) = 2 Then
'   Check  folder exists
    strPath = BASE_FLDR & arrPathParts(0)   ' N:Drawing Files\34947
    If Dir(strPath, vbDirectory) = arrPathParts(0) Then
'     Look for next folder beginning with the second lot of digits
      strPath = strPath & "\" & arrPathParts(1) & " *"   ' N:Drawing Files\34947\901 *
      Debug.Print "1. Looking for folder like: ", strPath
      strFolder = Dir(strPath, vbDirectory)
'     Check a folder is found
      If Len(strFolder) Then
        Debug.Print "Found: ", strFolder
'       Reset folder path
        strPath = BASE_FLDR & arrPathParts(0) & "\" & strFolder
'       Search for file
        strFile = arrPathParts(2) & "*.dft"
        Debug.Print "2. Looking for file like: ", strPath & "\" & strFile
        strFile = Dir(strPath & "\" & strFile)
'       Check file is found
        If Len(strFile) Then
          blRet = True
          Debug.Print "Found: ", strFile
          Application.FollowHyperlink strPath & "\" & strFile
        Else
          Debug.Print "File not found"
        End If
      Else
        Debug.Print "Folder not found"
      End If
    Else
      Debug.Print "Folder not found: ", strPath
    End If
  Else
    MsgBox "Document Name is not in correct format"
    Exit Function
  End If
  OpenFile = blRet
  
End Function


Then, in your click event, use:
CODE
Private Sub FileOpen_Click()

  If Len(Me.documentName & vbNullString) Then
    If Not OpenFile(Me.documentName) Then
      MsgBox "Can not find a file for " & Me.documentName
    End If
  End If

End Sub

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 10:57 AM
Post#15


UtterAccess Moderator
Posts: 11,904
Joined: 6-December 03
From: Telegraph Hill


Ah, cool! Glad you got there!

Always better to do it yourself! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 04:07 PM