Network paths

[David Montoya]

Dear,
I have a worksheet with multiple macros, which access different network resources; the problem I have it that when working from a Citrix environment the network resources are not the same, and I have to maintain two different version of the worksheet, one for each environment.

Is there a way to build into the macro a check to the regular network path, and if it is not reachable to change to the Citrix path? I mean similar as when if directory does not exist, then to create it, not that I want to create the directory, because it already exist.

The following are the begging of both environment paths:

Regular network path

\\wlsite\WLdepts$

Citrix environment path

\\Client\I$

In both environments, after the "$" is the regular directory\Folder\File names.

Thanks a lot.

 

[Jeffrey Mahoney]

Please try this function:

Sub TestCheckPath()
  Select Case CheckPaths
    Case 0
      MsgBox "No network folder found"
    Case 1
      MsgBox "Regular network folder found"
    Case 1
      MsgBox "Citrix newtwork folder found"
  End Select
  
End Sub


'Returns 0 for neither network
'Returns 1 for regular network
'Returns 2 for Citrix network
Function CheckPaths() As Integer
  Dim Paths(1 To 2) As String
  Dim X As Long
  Dim A As String
  
  Paths(1) = "\\wlsite\WLdepts\Some Folder\"
  Paths(2) = "\\Client\I\Some Folder\"
  
  For X = 1 To 2
    On Error Resume Next
    A = Dir(Paths(X), vbDirectory)
    On Error GoTo 0
    If A <> "" Then
      CheckPaths = X
      Exit Function
    End If
  Next X
  CheckPaths = 0
  
End Function

 

[David Montoya]

Jeffrey,
Thank you so much for your assistance; I have tested the function you provided, and it works.
Throughout the workbook, I have several instances where I need to access the network resources. Can you please let me know how can I integrate the proposed function with the current subs, so that I can access whichever network resources' path is available?
The following are the samples of my current macros depending on the resource; as mentioned, I am running two versions, one for the regular network, and the other for the Citrix environment:
Regular network:

    ChDir _
        "\\wlsite\WLdepts$\1Folder\2Folder\3Folder\4Folder\5Folder"
    Workbooks.Open Filename:= _
        "\\wlsite\WLdepts$\1Folder\2Folder\3Folder\4Folder\5Folder \Trade Data.xlsm"
    Sheets("Master DB").Select

Citrix environment:

    ChDir _
        "\\Client\I$\1Folder\2Folder\3Folder\4Folder\5Folder"
    Workbooks.Open Filename:= _
        "\\Client\I$\1Folder\2Folder\3Folder\4Folder\5Folder \Trade Data.xlsm"
    Sheets("Master DB").Select

Again, thank you for your help.

 

[Jeffrey Mahoney]

Notice that I changed the function a little. It now asks for the two paths instead of being hard coded. The NetworkChoice SUB is just an example on how you would use it. This allows you to have one SUB that can check the path it needs.

Make sense?

'Returns 0 for neither network
'Returns 1 for regular network
'Returns 2 for Citrix network
Function CheckPaths(RegPath As String, CitrixPath As String) As Integer
  Dim Paths(1 To 2) As String
  Dim X As Long
  Dim A As String
  
  Paths(1) = RegPath
  Paths(2) = CitrixPath
  
  For X = 1 To 2
    On Error Resume Next
    A = Dir(Paths(X), vbDirectory)
    On Error GoTo 0
    If A <> "" Then
      CheckPaths = X
      Exit Function
    End If
  Next X
  CheckPaths = 0
  
End Function


Sub NetworkChoice()
  Dim Paths(1 To 2) As String
  Dim WB As Workbook
  Dim TWB As Workbook
  Dim PathFile As String
  Dim NetworkType As Integer
  
  Set TWB = ThisWorkbook
  
  Paths(1) = "\\wlsite\WLdepts$\1Folder\2Folder\3Folder\4Folder\5Folder\"
  Paths(2) = "\\Client\I$\1Folder\2Folder\3Folder\4Folder\5Folder\"
  
  'Call the CheckPaths Function to get the right path
  NetworkType = CheckPaths(Paths(1), Paths(2))
  
  If NetworkType = 0 Then                       'No path available
    MsgBox "Paths provided are not available"
    Exit Sub
  End If
  
  PathFile = Paths(NetworkType) & "Trade Data.xlsm"
  ChDir Paths(NetworkType)
  Workbooks.Open Filename:=PathFile
  On Error Resume Next
  Set WB = ActiveWorkbook
  On Error GoTo 0
  If WB Is Nothing Then         'Didn't open properly
    MsgBox "There was a problem while trying to open the Trade Data workbook"
    Exit Sub
  End If
  WB.Sheets("Master DB").Select
  
  'More code here
End Sub

 

[David Montoya]

I am getting stuck at:

Workbooks.Open Filename:=PathFile

Run-time error '1004':
Microsoft Excel cannot access the file...

Here is the modification I have introduced to do a test:

'Returns 0 for neither network
'Returns 1 for regular network
'Returns 2 for Citrix network
Function CheckPaths(RegPath As String, CitrixPath As String) As Integer
  Dim Paths(1 To 2) As String
  Dim X As Long
  Dim A As String
  
  Paths(1) = RegPath
  Paths(2) = CitrixPath
  
  For X = 1 To 2
    On Error Resume Next
    A = Dir(Paths(X), vbDirectory)
    On Error GoTo 0
    If A <> "" Then
      CheckPaths = X
      Exit Function
    End If
  Next X
  CheckPaths = 0
  
End Function


Sub MasterDB()


Application.DisplayAlerts = False
Application.ScreenUpdating = False


For Each Sheet In Worksheets
    Sheet.Activate
    For Each Pic In ActiveSheet.Pictures
        Pic.Delete
    Next Pic
Next Sheet


    Sheets("Master DB").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
'---


'Sub NetworkChoice()
  Dim Paths(1 To 2) As String
  Dim WB As Workbook
  Dim TWB As Workbook
  Dim PathFile As String
  Dim NetworkType As Integer
  
  Set TWB = ThisWorkbook
  
  Paths(1) = "\\wlsite\WLdepts$\Administration\Trade Compliance\Import Compliance\Trade Data\Master Database"
  Paths(2) = "\\Client\I$\Administration\Trade Compliance\Import Compliance\Trade Data\Master Database"
  
  'Call the CheckPaths Function to get the right path
  NetworkType = CheckPaths(Paths(1), Paths(2))
  
  If NetworkType = 0 Then                       'No path available
    MsgBox "Paths provided are not available"
    Exit Sub
  End If
  
  PathFile = Paths(NetworkType) & "Trade Data.xlsm"
  ChDir Paths(NetworkType)
  Workbooks.Open Filename:=PathFile
  On Error Resume Next
  Set WB = ActiveWorkbook
  On Error GoTo 0
  If WB Is Nothing Then         'Didn't open properly
    MsgBox "There was a problem while trying to open the Trade Data workbook"
    Exit Sub
  End If


'---


  'More code here

 

[Jeffrey Mahoney]

Your two path strings need a backslash (\) at the end

Paths(1) = "\\wlsite\WLdepts$\Administration\Trade Compliance\Import Compliance\Trade Data\Master Database\"
Paths(2) = "\\Client\I$\Administration\Trade Compliance\Import Compliance\Trade Data\Master Database\"

 

[David Montoya]

Jeffrey,

Yes, that was the issue.

Thanks a lot for your guidance

 

[Jeffrey Mahoney]

You're welcome. Glad I could help.

 

[David Montoya]

Jeffrey,
After doing a review of my workbook, and trying to implement the solution, I have arrived at a situation that is a bit more complicated.
My workbook is compounded by 21 separate modules; in them, I have 49 instances of code where I have the network mappings. I can do the implementation of your solution to the ones that have the path and the instruction is to open a specific file; however, I have others that:

• Check if a folder exists, and saves a file in that directory.
• Insert pictures from a network folder into different worksheets
• Move files from a network folder to another network folder

I am wondering if there is a more generic solution such as the one I used to record the two separate versions, together with your network checkpaths solution. Let me explain:
I have the main version, which corresponds to the regular network, having all network mappings beginning with

[URL="file:///\\wlsite\WLdepts$"]\\wlsite\WLdepts$[/URL]

To save or create the Citrix version, I just use the “find and replace” throughout the “Current Project” changing

[URL="file:///\\wlsite\WLdepts$"]\\wlsite\WLdepts$[/URL]

per

[URL="file:///\\Client\I$"]\\Client\I$[/URL]

and saving the Citrix version on a different folder.
Maybe, if at opening the workbook your checkpaths solution triggers, and makes all the above mentioned changes; with one solution, I have hit all the 21 Modules and its 49 paths instances.

Thank you!

 

[Jeffrey Mahoney]

David,

Please consider this option. I changed the Function to read the Network Paths from two named ranges stored on a sheet called Setup. You will need to create those named ranges for this to work. I created a public variable called 'NetworkPath' that stores the chosen network path, either the regular network path or the citrix network path. All you have to do is use the NetworkPath variable throughout your project. The SUB named TestCheckPath is an example of how you would load the NetworkPath variable. You can call it once at the beginning or call it any time throughout the project.

If you need more paths besides the two, we could simply make a list and have the macro test them all.

Jeff

Public NetworkPath As String


Sub LoadNetworkPath()
  
  If NetworkPath = "" Then
    If CheckPaths() = 0 Then
      MsgBox "Error: No Network Path Found"
      Exit Sub
    End If
  End If
  
End Sub




'Returns 0 for neither network
'Returns 1 for regular network
'Returns 2 for Citrix network
'Save chosen path to public variable: NetworkPath
Function CheckPaths() As Integer
  Dim Paths(1 To 2) As String
  Dim X As Long
  Dim A As String
  Dim Setup As Worksheet
  
  Set Setup = ThisWorkbook.Sheets("Setup")
  
  Paths(1) = Setup.Range("RegNetworkPath").Value
  Paths(2) = Setup.Range("CitrixNetworkPath").Value
  
  For X = 1 To 2
    On Error Resume Next
    A = Dir(Paths(X), vbDirectory)
    On Error GoTo 0
    If A <> "" Then
      CheckPaths = X
      NetworkPath = Paths(X)
      Exit Function
    End If
  Next X
  NetworkPath = ""
  CheckPaths = 0
  
End Function