David Montoya

New Member
Joined
Apr 25, 2018
Messages
49
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
Code:
\\wlsite\WLdepts$

Citrix environment path
Code:
\\Client\I$

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

Thanks a lot.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Please try this function:

Code:
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

New Member
Joined
Apr 25, 2018
Messages
49
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:
Code:
    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:

Code:
    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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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?


Code:
'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

New Member
Joined
Apr 25, 2018
Messages
49

ADVERTISEMENT

I am getting stuck at:

Code:
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:

Code:
'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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Your two path strings need a backslash (\) at the end
Code:
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

New Member
Joined
Apr 25, 2018
Messages
49

ADVERTISEMENT

Jeffrey,

Yes, that was the issue.

Thanks a lot for your guidance
 

David Montoya

New Member
Joined
Apr 25, 2018
Messages
49
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
Code:
[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
Code:
[URL="file:///\\wlsite\WLdepts$"]\\wlsite\WLdepts$[/URL]
per
Code:
[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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top