How to fix Run-time Error 52 for Microsoft Excel 2003?

[Sherrin]

The following works well in local share network drive. However, when the file is transfer over to the open network (view from internet), the following excel file will show error: Run-time error: 52. Bad file name or number.

I have change the fpath to the following instead of fpath = ThisWorkbook.path & "\" but still having the same error.

Can anyone help? which code should I amend?

---------------------------------------------------------

' VBA code for one of the button created for user to click
 
Private Sub UserForm_Initialize()
fpath = "http://teamsite.UATnet.COM.US/sites/DHH/Shared Documents/ESMS - Enquiry" & "/"
 
KAS_file = "KAS EBook.xlsx"
LC_file = "LC EBook.xlsx"

Private Sub cmdDoc_Click()
If Uref <> "" Or Uref <> Null Or Lref <> "" Or Lref <> Null Then
    LC_Result = SearchnListFile(LC_file, "A", "B", "C", "D", "E", "F", "G", "H", 1)
    KAS_Result = SearchnListFile(KAS_file, "A", "B", "C", "D", "E", "F", "G", "H", 2)
    tbSearch.Text = ""
    tbSearch.SetFocus
    ref = ""
    If ThisWorkbook.Worksheets("SearchResult").Range("D3") = "" Then
        msgResponse = MsgBox("The data you're searching cannot be found under Legal Document Team.", , "Result")
    Else
        Unload SearchForm
        Unload ESBook
        DisplayArrange
    End If
 
ElseIf Uref = "" Or Lref = "" Then
    msgResponse = MsgBox("Sorry, you did not enter any data! Please enter a valid data.", , "Error")
End If
End Sub

' If the user click on the selected button, the following function will be executed.

Private Function SearchnListFile(file_name, letter1, letter2, letter3, letter4, _
                                 letter5, letter6, letter7, letter8, clr)
Dim file_path As String
Dim NoOfRow As Long
Dim temp As String
file_path = fpath & file_name
 
If Dir(file_path) <> "" Then
    Dim mth As Integer
    Dim box As Long
    Dim count As Integer
    count = 0
    NoOfRow = 0
End If
 
If clr = 1 Then
    ThisWorkbook.Worksheets("SearchResult").Range("A3:H6000").Clear
    Workbooks.Open Filename:=file_path, ReadOnly:=True
    Workbooks(file_name).Activate
    For mth = 1 To 3
        Worksheets(mth).Activate
        For box = 3 To 6000
            If ActiveSheet.Range("C" & box).Value = Uref Or _
            ActiveSheet.Range("D" & box).Value = Uref Or _
            ActiveSheet.Range("D" & box).Value = Lref Or _
            ActiveSheet.Range("E" & box).Value = Uref Or _
            ActiveSheet.Range("E" & box).Value = Lref Or _
            ActiveSheet.Range("G" & box).Value = Uref Or _
            ActiveSheet.Range("G" & box).Value = Lref Or _
            ActiveSheet.Range("H" & box).Value = Uref Or _
            ActiveSheet.Range("H" & box).Value = Lref Then
                count = count + 1
                ActiveSheet.Range("B" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter2 & count + 2)
                ActiveSheet.Range("C" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter3 & count + 2)
                ActiveSheet.Range("D" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter4 & count + 2)
                ActiveSheet.Range("E" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter5 & count + 2)
                ActiveSheet.Range("F" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter6 & count + 2)
                ActiveSheet.Range("G" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter7 & count + 2)
                ActiveSheet.Range("H" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter8 & count + 2)
            ElseIf ActiveSheet.Range("B" & box).Value = Empty And _
                   ActiveSheet.Range("C" & box).Value = Empty And _
                   ActiveSheet.Range("D" & box).Value = Empty And _
                   ActiveSheet.Range("E" & box).Value = Empty And _
                   ActiveSheet.Range("F" & box).Value = Empty And _
                   ActiveSheet.Range("G" & box).Value = Empty And _
                   ActiveSheet.Range("H" & box).Value = Empty Then Exit For
        Exit For
            End If
    Next
Next
    Workbooks(file_name).Close SaveChanges:=False
 
ElseIf clr = 2 Then
    ThisWorkbook.Worksheets("SearchResult").Activate
    For box = 3 To 6000
        If ActiveSheet.Range("D" & box).Value = Uref Or _
        ActiveSheet.Range("D" & box).Value = Lref Then
            NoOfRow = NoOfRow + 1
        End If
    Next
    Workbooks.Open Filename:=file_path, ReadOnly:=True
    Workbooks(file_name).Activate
    For mth = 1 To 3
        Worksheets(mth).Activate
        For box = 3 To 6000
            If ActiveSheet.Range("C" & box).Value = Uref Or _
            ActiveSheet.Range("D" & box).Value = Uref Or _
            ActiveSheet.Range("D" & box).Value = Lref Or _
            ActiveSheet.Range("E" & box).Value = Uref Or _
            ActiveSheet.Range("E" & box).Value = Lref Or _
            ActiveSheet.Range("G" & box).Value = Uref Or _
            ActiveSheet.Range("G" & box).Value = Lref Or _
            ActiveSheet.Range("H" & box).Value = Uref Or _
            ActiveSheet.Range("H" & box).Value = Lref Then
                count = count + 1
                ActiveSheet.Range("B" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter2 & count + NoOfRow + 2)
                ActiveSheet.Range("C" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter3 & count + NoOfRow + 2)
                ActiveSheet.Range("D" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter4 & count + NoOfRow + 2)
                ActiveSheet.Range("E" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter5 & count + NoOfRow + 2)
                ActiveSheet.Range("F" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter6 & count + NoOfRow + 2)
                ActiveSheet.Range("G" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter7 & count + NoOfRow + 2)
                ActiveSheet.Range("H" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter8 & count + NoOfRow + 2)
            ElseIf ActiveSheet.Range("B" & box).Value = Empty And _
                   ActiveSheet.Range("C" & box).Value = Empty And _
                   ActiveSheet.Range("D" & box).Value = Empty And _
                   ActiveSheet.Range("E" & box).Value = Empty And _
                   ActiveSheet.Range("F" & box).Value = Empty And _
                   ActiveSheet.Range("G" & box).Value = Empty And _
                   ActiveSheet.Range("H" & box).Value = Empty Then Exit For
            End If
    Next
Next
 
Workbooks(file_name).Close SaveChanges:=False
 
ElseIf clr = 3 Then
    ThisWorkbook.Worksheets("SearchResult").Activate
    For box = 3 To 6000
        If ActiveSheet.Range("D" & box).Value = Uref Or _
        ActiveSheet.Range("D" & box).Value = Lref Then
            NoOfRow = NoOfRow + 1
        End If
    Next
 
    Workbooks.Open Filename:=file_path, ReadOnly:=True
    Workbooks(file_name).Activate
    For mth = 4 To 4
        Worksheets(mth).Activate
        For box = 3 To 10000
            If ActiveSheet.Range("D" & box).Value = Uref Or _
            ActiveSheet.Range("D" & box).Value = Lref Then
                count = count + 1
                ActiveSheet.Range("B" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter2 & count + NoOfRow + 2)
                ActiveSheet.Range("C" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter3 & count + NoOfRow + 2)
                ActiveSheet.Range("D" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter4 & count + NoOfRow + 2)
                ActiveSheet.Range("E" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter5 & count + NoOfRow + 2)
                ActiveSheet.Range("F" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter6 & count + NoOfRow + 2)
                ActiveSheet.Range("G" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter7 & count + NoOfRow + 2)
                ActiveSheet.Range("H" & box).Copy _
                Destination:=ThisWorkbook.Worksheets("SearchResult"). _
                Range(letter8 & count + NoOfRow + 2)
            ElseIf ActiveSheet.Range("B" & box).Value = Empty And _
                   ActiveSheet.Range("C" & box).Value = Empty And _
                   ActiveSheet.Range("D" & box).Value = Empty And _
                   ActiveSheet.Range("E" & box).Value = Empty And _
                   ActiveSheet.Range("F" & box).Value = Empty And _
                   ActiveSheet.Range("G" & box).Value = Empty And _
                   ActiveSheet.Range("H" & box).Value = Empty Then Exit For
            End If
    Next
Next
 
Workbooks(file_name).Close SaveChanges:=False
Else
    msgResponse = MsgBox("File cannot be found in path: " & file_path, , "Error")
End If
 
 
End Function

[zmbd]

First code block Userform_Intialize:
Hopefully a typo; however, no end sub
In anycase, from what you have shown, the value of line-4 is local in scope to this procedure; thus at the end, is lost to the remaining procedures. This holds true for Lines 6 and 7

Second code block CmdDoc_Click
Lines 3 and 4 refer to values that you have not defined in the local scope, from what you've shown...

Third code block SearchListFile
Because the values in the second block are not defined then the values that Line-6 refers to values not set within the local scope - once again, from what information you have provided us...

Line-8, to the best of my understanding, DIR() does not work on HTML, in fact, I donot believe that the DIR() even supports UNC path conventions.


You might try using the external data method
Support.office: Connect external data to your workbook -- From Web Make sure you name the connection

The nice thing here is that you can open a new workbook,
Excel2013>Ribbon>View>Macros>MacrosDropDown>Record Macro

Then use the above method to generate your base code to link to the workbook. Make sure that you include code to remove the link once its use is over or you may have some irate users as each time the code runs it will attempt to add the link and as it already exists you will either create a bandwidth overload or the name may already be in the workbooks links creating an error.