New VBA won't open file on Network Drive

[John Sadler]

Hi folks tried this:

Workbooks.Open Filename:="\\FS01\1300Shared\Marketing Folder\scorecard.xls"

Error: Sorry we couldn't find \\FS01\1300Shared\Marketing Folder\scorecard.xls. It is possible it was moved, renamed or deleted.

Also doesn't work using the Drive letter

Workbooks.Open Filename:="Z:\Marketing Folder\scorecard.xls"

I have other VBA where similar line (see below) is working so I'm baffled

Workbooks.Open Filename:="W:\NPS\Reports\NPS Monthly Snapshots Current FY.xlsm" <<<< This works

Also interesting (for me at least) is that I tried using the Macro Recorder when opening the file and all I got was:

Sub Macro3()
'
' Macro3 Macro
'
End Sub

Hope someone can help me.

 

[Trebor76]

Hi John,

If Z is mapped as \\FS01\1300Shared\Marketing Folder\ it should work.

What happens if you put the mapping into Window Explorer? Is the mapping valid? If so can you see the file "scorecard.xls" (which is a very old version of Excel!!) there?

Robert

 

[John Sadler]

Howdy ... yes all mapped correctly.
Cheers John

 

[Trebor76]

What about copying \\FS01\1300Shared\Marketing Folder\ into Windows Explorer and see what that does?

 

[John Sadler]

Pasting into Explorer opens the Marketing Folder

 

[Trebor76]

And in that folder you see the file scorecard.xls and if so does it open if you double click on it?

 

[John Sadler]

Yep ... opens no problem at all. I tried the VBA with several files and all got the same error

This code, however works

Workbooks.Open Filename:="W:\NPS\Reports\NPS Monthly Snapshots Current FY.xlsm"

Cheers

 

[Trebor76]

If you press F2 on the "scorecard.xls" file via Windows Explorer - there's no leading or trailing spaces around the file name?

 

[Trebor76]

Also see if the filename is outputted in Col. A of the active sheet via this code:

Option Explicit
Sub Macro1()
    
    Dim strPath As String
    Dim strFile As String
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    strPath = "\\FS01\1300Shared\Marketing Folder\"
    strFile = Dir$(strPath & "*.xls")
    lngMyRow = 2
    
    Do While Len(strFile)
        Range("A" & lngMyRow).Value = strFile
        strFile = Dir$
        lngMyRow = lngMyRow + 1
    Loop
    
    Application.ScreenUpdating = True

End Sub

Robert