Network Drive Path ? Help

0 Votes

Network Drive Path ? Help


I have in a standalone Pc the following statements (working perfectly):
Dim wSht As Worksheet
Dim wBk As Workbook
Sheets("OnHand").Cells(5, 6) = 1
Set wBk = Workbooks("_14KbWC126Ot.xls")
Set wSht = Sheets("SchedulingPanel")
wSht.Copy before:=wBk.Sheets("OT")
Sheets("SchedulingPanel (2)").Select
Sheets("SchedulingPanel (2)").Name = "SchedulingPanel"
Sheets("OnHand").Cells(5, 6) = 0

Wen I install the two Excel files in a server (Network Drive), I get the error code 9 "Subscript out of range" (because the Path is not correct) in the following statement:
Set wBk = Workbooks("_14KbWC126Ot.xls")

Can somebody help me and indicate to me how to define the Path in a Network Drive?

0 Votes
Collapse -

I believe the issue may be related to where you are opening the files from.

I assume from your post that you copied the two files to a network share, and then attempted to launch them from your local machine. If that is the case, then you may want to provide the full network path in the statement that opens the workbook:

Replace this:
Set wBk = Workbooks("_14KbWC126Ot.xls")

With this:
Set wBk = Workbooks("\\YOURSERVER\YOURFOLDERPATH\_14KbWC126Ot.xls")

Or, if you have a mapped network drive, then do this:
Set wBk = Workbooks("X:\YOURFOLDERPATH\_14KbWC126Ot.xls")

Where X is your mapped network folder.

The issue is that although the file is sitting in the network folder, you are using your local copy of Excel to open it, so when Excel executes the VBA, it is looking for your file (_14KbWC126Ot.xls) in the executable directory (e.g. c:\program files\Microsoft Office\).

I hope that helps.