ExecuteExcel4Macro , Is there any way to reference worksheet by index instead of its name in Application.ExecuteExcel4Macro

Question

I would like to reference worksheet by index method for the worksheet without open the workbook. However, I don't know how to do it for this. Many example from online is using "Sheet1", which is not my case. For my case, each workbook has its worksheetname, They are all named differently.

Sub excelfor_macro()
Dim p As String, f As String
Dim s As String, a As String
Dim my_array(1 To 8) As String
Dim r As Integer

Application.ScreenUpdating = False
p = "C:\Users\puntek\Desktop\"
f = "490XXZMAV31002S84D6A_002S84D68.xlsx"
s = SHEETNAME(1).Name  **'This is where, i want to reference by index, not worksheet name**

For r = 1 To 8
    a = Cells(r + 8, 3).Address 'start from row 9 the info
    my_array(r) = GetValue(p, f, s, a)
Next r
End Sub

Private Function GetValue(path, file, sheet, ref) 'This is getvalue function, the worksheetname will pass to sheet
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

Answer 1

Open the workbook to get the name:

Dim wb as Workbook
p = "C:\Users\puntek\Desktop\"
f = "490XXZMAV31002S84D6A_002S84D68.xlsx"
Set wb = Workbooks.Open(p & f) ' or Set wb = Workbooks.Add(p & f)
s = wb.Worksheets(1).Name
' optionally close the workbook
wb.Close