VBA full file path

[baitmaster]

Hi, I'm using VBA to select a file and get the path to that file

strSourceFilePath = Application.GetOpenFilename("Excel Files, *.xls; *.xlsm; *.xlsx")

The file I'm creating will be shared on a network, so I don't want it to capture drives mapped on individual users PCs, e.g. C:\... or M:\..., but the full network location \\network\blah\blah

How can I force the path identified to be a full network location instead of a shortcut location?

 

[mrmmickle1]

Try using these to set the drive and directory before that other bit of code:

ChDrive ("M")
ChDir ("M:\Operations\FilePathName\Etc..")

 

[JimSnyder]

This is from AdamsTips on StackOverflow.com:

[COLOR=#00008B][/COLOR]Public Sub TestNetShareName()


    Dim NetworkObject As Object
    Dim FSO As Object
    Dim Directory As Object
    Dim Filename As Object
    Dim ServerShare As String
    Dim UserName As String
    Dim Password As String


    ServerShare = "\\corp-server\HostingFolder"
    UserName = "mydomain\myuser"
    Password = "freddie123"


    Set NetworkObject = CreateObject("WScript.Network")
    Set FSO = CreateObject("Scripting.FileSystemObject")


    NetworkObject.MapNetworkDrive "", ServerShare, False, UserName, Password


    Set Directory = FSO.GetFolder(ServerShare)
    For Each Filename In Directory.Files
        Debug.Print Filename.Name
    Next


    Set Filename = Nothing
    Set Directory = Nothing
    Set FSO = Nothing


    NetworkObject.RemoveNetworkDrive ServerShare, True, False


    Set NetworkObject = Nothing


End Sub

 

[JimSnyder]

A link to using UNC:
Universal network drive mapping with Excel 2010 VBA | dedicatedexcel.com

EDIT:
I have used UNC in the past where the workbook with the macro was being moved and the drive letter was never the same.

 

[baitmaster]

A link to using UNC:
Universal network drive mapping with Excel 2010 VBA | dedicatedexcel.com

EDIT:
I have used UNC in the past where the workbook with the macro was being moved and the drive letter was never the same.

This is the problem I am working to fix, but I can't hard-code the network location in my VBA script. I need to capture the full network location of whatever file is selected by the user, and then re-use it later. I still can't see how to do this. Different users will have network locations mapped differently, but the resulting path captured must be stored in a file for audit purposes and then re-used by later scripts. Any user may select the source file when configuring the system, and any other user may reuse it later