Open File Location

[Gismo]

Hi all,

I am using the below code to open a specific file location on the network but it opens "My Documents" on the local C:\ drive
Any suggestions please

Private Sub cmdBrowse_Click()

    On Error GoTo Err_Handler
    
    Dim SB_Attachment As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer
    
    SB_Attachment = BrowseFile
    
    If SB_Attachment <> "" Then
        ' get folder and file names
        intPos = InStrRev(SB_Attachment, "\\Sjo2054\shared\Data_Files\")
        strFolder = Left(SB_Attachment, intPos - 1)
        strFile = Mid(SB_Attachment, intPos + 1)
        
        ' populate text boxes on form
        Me.SB_Attachment = SB_Attachment
      
    End If
    
Exit_Here:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub

 

[zeroaccess]

What happens when you hit Windows + R and paste in \\Sjo2054\shared\Data_Files\ and hit ok?

 

[Gasman]

What does Browsefile do?
Please show the code.

 

[Gismo]

What does Browsefile do?
Please show the code.

Public Function BrowseFile() As String

Dim strFile As String
With Application.FileDialog(1)
.Title = "Select File"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No file selected", vbInformation
Exit Function
End If
End With

BrowseFile = strFile

End Function

 

[Gasman]

Set the InitialFileName ?

https://stackoverflow.com/questions/16917122/defaulting-a-folder-for-filedialog-in-vba

 

[Gismo]

Set the InitialFileName ?

https://stackoverflow.com/questions/16917122/defaulting-a-folder-for-filedialog-in-vba

where?
how?
Please

 

[Gasman]

See the link.
As you are using it within a function, pass your desired start path into that function.

 

[Gismo]

See the link.
As you are using it within a function, pass your desired start path into that function.

I llooked at the link you send, still not sure how implement it or to adapt the code to mine

 

[theDBguy]

I llooked at the link you send, still not sure how implement it or to adapt the code to mine

Hi. Pardon me for jumping in. @Gasman must be offline. I think this is what he meant.

Public Function BrowseFile() As String

    Dim strFile As String
    With Application.FileDialog(1)
        .Title = "Select File"
        .InitialFileName = "c:\foldername" '<<== put your specific folder path here
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbInformation
            Exit Function
        End If
    End With
    
    BrowseFile = strFile
    
End Function

Hope that helps...

 

[Gismo]

Hi. Pardon me for jumping in. @Gasman must be offline. I think this is what he meant.

Public Function BrowseFile() As String

    Dim strFile As String
    With Application.FileDialog(1)
        .Title = "Select File"
        .InitialFileName = "c:\foldername" '<<== put your specific folder path here
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbInformation
            Exit Function
        End If
    End With
   
    BrowseFile = strFile
   
End Function

Hope that helps...

Perfect, thank you very much

 

[theDBguy]

Perfect, thank you very much

Hi. You're welcome. @Gasman and I were happy to assist. Good luck with your project.

 

[Gasman]

Sorry, sunbathing in the garden.

Not quite. This is what I was thinking of, however I have found that the FileDialog will use the last folder opened if no path supplied.

Private Sub cmdBrowse_Click()

    On Error GoTo Err_Handler
 
    Dim SB_Attachment As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer
 
    SB_Attachment = BrowseFile("C:\Temp\")
 
    If SB_Attachment <> "" Then
        ' get folder and file names
        intPos = InStrRev(SB_Attachment, "\\Sjo2054\shared\Data_Files\")
        strFolder = Left(SB_Attachment, intPos - 1)
        strFile = Mid(SB_Attachment, intPos + 1)
     
        ' populate text boxes on form
        SB_Attachment = SB_Attachment
   
    End If
 
Exit_Here:
    Exit Sub
 
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub
Public Function BrowseFile(Optional strStartPath As String) As String

Dim strFile As String
With Application.FileDialog(1)
    .Title = "Select File"
    .InitialFileName = strStartPath
    If .Show Then
        strFile = .SelectedItems(1)
    Else
        MsgBox "No file selected", vbInformation
        Exit Function
    End If
End With

BrowseFile = strFile

End Function

Edit: ALso if you are just using a folder, walways complete the path with a "\", else the Filedailog puts the last folder name as the filename.