VBA code that references another directory if one does not work

[willow1985]

I have a spreadsheet that contains a macro that when run opens various other spreadsheets, grabs their data and closes them. However this main spreadsheet is accessed by multiple users in 2 different ways on a network. Sometimes the drive can appear as X and other times as an address like 190.165.1.22\

Basically what I am looking for is if the macro runs into an error where it cannot find the drive (I am assuming this is the problem because I am getting a Subscript out of range Error) that it will try the 2nd directory (see VBA code)

Any help would be greatly appreciated!

Dim CAPAD As String

Dim CCD As String

Dim CID As String

Dim NCRD As String

Dim ASD As String

Dim CAPAN As String

Dim CCN As String

Dim CIN As String

Dim NCRN As String

Dim ASN As String



CAPAD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B15").Value ‘ or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B22").Value

CCD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B16").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B23").Value

CID = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B17").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B24").Value

NCRD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B18").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B25").Value

ASD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B19").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B26").Value



CAPAN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B29").Value

CCN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B30").Value

CIN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B31").Value

NCRN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B32").Value

ASN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B33").Value

Quality Dashboard.xlsm
	A	B
14	Directory 1
15	CAPA Directory	\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\CAPA findings\CAPA Log.xlsm
16	CC Directory	\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\Customer Concern - Warranty Request Log.xlsm
17	CI Directory	\\190.165.1.22\com\Lean Folder\Continuous Improvement Database.xlsm
18	NCR Directory	\\190.165.1.22\cad\QUALITY\Group\Supplier Non-Conformance Reports\Supplier NCR Log.xlsm
19	Audit Schedule Directory	\\190.165.1.22\cad\QUALITY\Group\Audit\Internal Audit Schedule.xlsm
20
21	Directory 2
22	CAPA Directory	X:\QUALITY\Group\Corrective Actions\CAPA findings\CAPA Log.xlsm
23	CC Directory	X:\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\Customer Concern - Warranty Request Log.xlsm
24	CI Directory	S:\Lean Folder\Continuous Improvement Database.xlsm
25	NCR Directory	X:\QUALITY\Group\Supplier Non-Conformance Reports\Supplier NCR Log.xlsm
26	Audit Schedule Directory	X:\QUALITY\Group\Audit\Internal Audit Schedule.xlsm
27
28	File Names
29	CAPA	CAPA Log.xlsm
30	CC	Customer Concern - Warranty Request Log.xlsm
31	CI	Continuous Improvement Database.xlsm
32	NCR	Supplier NCR Log.xlsm
33	Audit Schedule	Internal Audit Schedule.xlsm
Graph Data

Cell Formulas
Range	Formula
B15	B15	="\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\CAPA findings\"&B29
B16	B16	="\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\"&B30
B17	B17	="\\190.165.1.22\com\Lean Folder\"&B31
B18	B18	="\\190.165.1.22\cad\QUALITY\Group\Supplier Non-Conformance Reports\"&B32
B19	B19	="\\190.165.1.22\cad\QUALITY\Group\Audit\"&B33
B22	B22	="X:\QUALITY\Group\Corrective Actions\CAPA findings\"&B29
B23	B23	="X:\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\"&B30
B24	B24	="S:\Lean Folder\"&B31
B25	B25	="X:\QUALITY\Group\Supplier Non-Conformance Reports\"&B32
B26	B26	="X:\QUALITY\Group\Audit\"&B33

 

[Micron]

In Access it's customary to use UNC file paths rather than drive volume letters in a multi user network environment, so that would be my suggestion.

 

[willow1985]

In Access it's customary to use UNC file paths rather than drive volume letters in a multi user network environment, so that would be my suggestion.

I do not know what a UNC file path is, if that is the number file path then sometimes it does not work so I wanted the letter backup

 

[Micron]

The number set looks like an IP address to me. Can't say I've ever seen that used for a network path but I guess it's possible. The letter is like a nickname for a drive in a server. Google UNC paths or ask your IT guy what server name is, or you might be able to discover at the server/drive properties sheet in File Explorer.

What Does Universal Naming Convention (UNC) Mean?

The Universal Naming Convention (UNC) is a standard for identifying shared resources on a network, such as printers and servers.

www.lifewire.com

 

[willow1985]

The number set looks like an IP address to me. Can't say I've ever seen that used for a network path but I guess it's possible. The letter is like a nickname for a drive in a server. Google UNC paths or ask your IT guy what server name is, or you might be able to discover at the server/drive properties sheet in File Explorer.

What Does Universal Naming Convention (UNC) Mean?

The Universal Naming Convention (UNC) is a standard for identifying shared resources on a network, such as printers and servers.

www.lifewire.com

IT advised that is the server name. it is a number followed by the word cad: \\190.165.1.22\cad\
But it still has not been working remote.

Regardless is there a way to reference 2 locations?

the first however if there is an error reference the 2nd?

 

[Micron]

I have no doubt it's possible but I have no idea what you consider the 2nd to be. What is the alternate (or as you say, 2nd) to row 15? Row 16 or row 22 - or something else? If you are going to take a stab at it, create an error handler and trap error 9. Then either in the error handler you could do an Offset and get the other path, or you could Resume Next - where you then test if the error property is 9 in the main body of the code. If so, retrieve the alternate path. That suggestion assumes the next choice won't error. If doing Offset, you might want to have a cell with a note that says "do not alter the relationships of these rows" or something similar.

This would be another way to write your code (less typing)

Dim CAPAD As String, CCD As String, CID As String, NCRD As String
Dim ASD As String, CAPAN As String, CCN As String, CIN As String
Dim NCRN As String, ASN As String

With Workbooks("Quality Dashboard").Sheets("Graph Data")
   CAPAD = .Range("B15") ' or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B22").Value
   CCD = .Range("B16") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B23").Value
   CID = .Range("B17") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B24").Value
   NCRD = .Range("B18") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B25").Value
   ASD = .Range("B19") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B26").Value
   CAPAN = .Range("B29")
   CCN = .Range("B30")
   CIN = .Range("B31")
   NCRN = .Range("B32")
   ASN = .Range("B33")
End With

.Value is optional. I rarely use it in Access vba & so far, don't see a compelling reason to use it in Excel vba either.
In any event, the reason the numeric references (server names) fail might be due to something else. If I wanted to pursue that, I'd start typing that string into a File Explorer dialog and see where it gets me.

EDIT - don't know what you mean by "remote" either. Working from home?
Also if needing help with getting the alternate path, the rest of the code is probably needed.

 

[Micron]

Without knowing how you raise the error, here is the gist of one approach that uses an error handler. If you try it and it doesn't work, the rest of your code should clear up the questions. I think I figured out from you code comments that the alternate path is 7 rows down so I went with that. IMO you would want to step through the code to completion lest you end up in a loop over error 9. A counter might be a wise addition so that if that error gets raised (e.g. 2 times) code should stop trying.

Dim CAPAD As String, CCD As String, CID As String, NCRD As String
Dim ASD As String, CAPAN As String, CCN As String, CIN As String
Dim NCRN As String, ASN As String

On Error GoTo errHandler
i = 0 'not really needed, but if debugging code, i can be something other than zero

setPaths:
With Workbooks("Quality Dashboard").Sheets("Graph Data")
   CAPAD = .Range("B15").Offset(i,0)
   CCD = .Range("B16").Offset(i,0)
   CID = .Range("B17").Offset(i,0)
   NCRD = .Range("B18").Offset(i,0)
   ASD = .Range("B19").Offset(i,0)
   CAPAN = .Range("B29")
   CCN = .Range("B30")
   CIN = .Range("B31")
   NCRN = .Range("B32")
   ASN = .Range("B33")
End With

exitHere:
Exit Sub

errHandler:
If err.Number = 9 Then 
  i = 7
  Resume setPaths
Else
  "Error " & Err.Number & ": " & Err.Description
  Resume exitHere ' just a guess
End If

 

[Tupe77]

Is this the right solution for you?

This only checks for the existence of the network path "\\ 190.165.1.22 \ cad" at the beginning of the macro. If the network path is not found, it switches to the Directory2 option.

Sub test_networkpath
Dim CAPAD As String, CCD As String, CID As String, NCRD As String
Dim ASD As String, CAPAN As String, CCN As String, CIN As String
Dim NCRN As String, ASN As String
  
 Dim fso As Object
 Set fso = CreateObject("scripting.filesystemobject") 

    If fso.FolderExists("\\190.165.1.22\cad") Then
        With Workbooks("Quality Dashboard").Sheets("Graph Data")
           CAPAD = .Range("B15") ' or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B22").Value
           CCD = .Range("B16") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B23").Value
           CID = .Range("B17") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B24").Value
           NCRD = .Range("B18") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B25").Value
           ASD = .Range("B19") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B26").Value
           CAPAN = .Range("B29")
           CCN = .Range("B30")
           CIN = .Range("B31")
           NCRN = .Range("B32")
           ASN = .Range("B33")
        End With
    Else
        With Workbooks("Quality Dashboard").Sheets("Graph Data")
           CAPAD = .Range("B22") ' or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B22").Value
           CCD = .Range("B23") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B23").Value
           CID = .Range("B24") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B24").Value
           NCRD = .Range("B25") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B25").Value
           ASD = .Range("B26") 'or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B26").Value
           CAPAN = .Range("B29")
           CCN = .Range("B30")
           CIN = .Range("B31")
           NCRN = .Range("B32")
           ASN = .Range("B33")
        End With
    End If

end sub

 

[Tupe77]

The solution in my previous message will answer your question, but may not resolve your issue. Drive letter X is probably the path to the server's 190.165.1.22 folder. In that case, if the path "\\190.165.1.22\cad\" does not work, then usually drive letter x should not work either.

However, without knowing your environment, I can’t be sure, so maybe the easiest thing to do is try the macro?

 

[willow1985]

Thank you all for the information. After reading everything you wrote, expecially Tupe77 information that X would not work if the server path does not work got me thinking. It turned out the settings were changed on my pc the last time IT was in it to include file extensions so the macro was looking for the file extension of my main book "Quality Dashboard". A quick change in settings fixed everything.

Thank you all so much!