Access & sage folder name convention

[FJM]

I am using some imported ODBC connected SAGE tables in my database and have a problem when trying to link some types of part numbers.
The SAGE numbers can contain "/" as part of their make up and I will use XYZ/123 as an example to try to explain what happens
aDDITIONAL Information about part number XYZ/123 is kept in a folder in SAGE which is named AXYZ#2F123. (The A in front of the partnumber shows that the detail is an attachment in SAGE)
The "/" in the part number has been replaced by "#2F" in the folder name.
What I want to do now is to call up the folder in SAGE from the access database by clicking on the partnumber when it is viewed in the database.
If I change the partnumber to XYZ#2F123 in the database it will not match up with the folder.


Can anyone help with the convention which will allow the connection to be made

[ranman256]

vFolder = mid([part],2)
VFolder = Replace(vFolder,"/","#2F")

vFile = "\\server\" & vFolder & "\myfile.doc"

[FJM]

I've attempted to use the replace function but the code is unable to locate the folder containing the "#" symbol, please note that I'm using Access 2007, see below the code I am using;

Public Function RunAddtionalInfo5()
Dim ThePath As String
Dim ThePARTNUMBER As String
Dim ThePARTnumber2 As String
Dim SlashCheck As Integer
Dim StringLength As Integer

ThePARTNUMBER = "\" & [Forms]![inspectionsheet]![PARTNUMBER]

SlashCheck = InStr(ThePARTNUMBER, "/")
StringLength = Len(ThePARTNUMBER)

If SlashCheck <> 0 Then
ThePARTNUMBER = Replace(ThePARTNUMBER, "/", "HASH2f")
ThePARTNUMBER = Replace(ThePARTNUMBER, "HASH", Chr(35))
End If
ThePath = "\\SERVERFOUR\Quality System\NCR Analysis\PART NUMBER"
ThePath = ThePath & ThePARTNUMBER
ThePath = "\\SERVERFOUR\Quality System\NCR Analysis\PART NUMBER" & "H205MMSS-34" & Chr(35) & "2f34-N-NUT"
On Error GoTo errorhandler
Call Shell("explorer.exe" & "\\SERVERFOUR\Quality System\NCR Analysis\PART NUMBER" & "H205MMSS-34" & Chr(35) & "2f34-N-NUT", vbNormalFocus)
Application.FollowHyperlink ThePath
Exit Function
errorhandler:
MsgBox "No folder exists", vbCritical, "Error Message"
Exit Function

End Function


I have done some searching on the web for reasons why the code cannot find folders containing "#" and have found this link, http://stackoverflow.com/questions/2...icked-in-excel. It seems that MS applications don't accept the"#" symbol in hyperlinks and the only methods of opening folders with Windows Explorer in Access is to either follow hyperlinks or using a shell(http://www.devhut.net/2011/08/31/ms-...owse-a-folder/). Can you advise if there are any alternative methods on opening folders or somehow replacing the "#" symbol with a character or phrase which can be searched using Windows Explorer?

[ItsMe]

EDIT: Forgot to mention you need a reference to Microsoft Scripting Runtime

I played around with the idea a little and it seems to me that the easiest would be to copy the files to a temp folder local to the user. Here is an example of the code. Of course, at some point, you will need to delete any files in the temp folder ...

Dim strPath As String
strPath = "C:\Test\Folder#With\"

Dim objFSO As New FileSystemObject
Dim objFolder As Folder
Dim objFile As File

    Set objFolder = objFSO.GetFolder(strPath)

        For Each objFile In objFolder.Files
        
            objFile.Copy "C:\Test\Destination\"
            
        Next
        
    FollowHyperlink "C:\Test\Destination\"
    
Set objFolder = Nothing
Set objFSO = Nothing