Macro not working when using on another sheet

[winstela]

Morning everyone

I am hoping this is a quick one to resolve for the experts here.

I have a code that is using a cell reference as part of a string to open another workbook.

When I run the code from the same sheet (Template) that holds the reference its work, but when I try to run the code from a different worksheet (Home) in the same workbook, the message cannot find file pops up (as per the code)

I have tried references Thisworkbook but get the same results. its seems that the (template) sheet needs to be activate but I don't want the user to see that. I want the user to see (Home) sheet until the other workbook has opened.

Heres my code

Sub OpenLatestFile()

    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim sFolderPath As String
Dim sIndexNumber As String
 Dim wb As Workbook


' Get the index number
sIndexNumber = Sheets("Template").Cells(1, 12)
    
' Create the file path using the IndexNumber
sFolderPath = "\\mcuk-adc\Prod_Results\01 Value Stream\01 Bowler\" + Mid(sIndexNumber, 1, 4) + "\" + Mid(sIndexNumber, 5, 5) + "\" + Cells(1, 11) + "\"
    
    'Specify the path to the folder
    MyPath = sFolderPath
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xls", vbNormal)
    

    If Len(MyFile) = 0 Then
        MsgBox "Sorry No files were found...", vbExclamation
        Exit Sub
    End If
    
  
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
     On Error GoTo ErrorHandler
'Call function to check if the file is open
If Not wbOpen(MyFile, wb) Then Workbooks.Open MyPath & LatestFile, ReadOnly:=True


ErrorHandler:
Exit Sub

End Sub

Its being used with this function

Function wbOpen1(MyFile As String, wbO As Workbook) As Boolean
    On Error Resume Next
    Set wbO = Workbooks(MyFile)
    wbOpen1 = Not wbO Is Nothing
End Function

Thanks

 

[rollis13]

In this line:

sFolderPath = "\\mcuk-adc\Prod_Results\01 Value Stream\01 Bowler\" + Mid(sIndexNumber, 1, 4) + "\" + Mid(sIndexNumber, 5, 5) + "\" + Cells(1, 11) + "\"

Cells(1, 11) doesn't refer to a specific sheet and will work only when the macro is launched from sheet Template. Change it to:

sFolderPath = "\\mcuk-adc\Prod_Results\01 Value Stream\01 Bowler\" + Mid(sIndexNumber, 1, 4) + "\" + Mid(sIndexNumber, 5, 5) + "\" + Sheets("Template").Cells(1, 11) + "\"

 

[winstela]

Thankyou rollis13, that has worked perfectly!

Appreciate the quick response

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.