Adressing the first sheet

[Wil Moosa]

The code following extracts data from two different cells on sheet1(Annemie.Holland) into one cell on sheet2(1). It is meant to repair similar files in one folder.

The problem here is that it adresses sheet1 by the name "Annem.Hollander". This does not work when 20 files have a sheet1 with a different name here. Is there another way to adress sheet1?

Sub openfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\Wil Moosa\Desktop\Updater\Vakantiekaart"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Repair month january
Sheets("1").Select
Sheets("1").Unprotect
Range("D11:F11").Select
ActiveSheet.Unprotect
Range("D11:F11").Select
ActiveCell.FormulaR1C1 = _
"='Annem. Hollander - '!R[7]C+'Annem. Hollander - '!R[9]C"
Range("D12:F12").Select
Sheets("1").Protect

'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub

 

[Wil Moosa]

Instead of the tab name I want to adress the tab by -as i just learned- the index value of the tab. But how do I manage so with the formula above?

 

[Wil Moosa]

No answer so far; is this such a tricky question or is it just not possible?

 

[jmthompson]

Not tested, but try

Sub openfilesInALocation()
Dim i As Integer, wb As Workbook
Dim MySheet as String
With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\Wil Moosa\Desktop\Updater\Vakantiekaart"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Repair month january
MySheet = Sheets(1).Name
Sheets("1").Select
Sheets("1").Unprotect
Range("D11:F11").Select
ActiveSheet.Unprotect
Range("D11:F11").Select
ActiveCell.FormulaR1C1 = _
        "='" & MySheet & "'!R[7]C+'" & MySheet & "'!R[9]C"
Range("D12:F12").Select
Sheets("1").Protect

'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub

 

[shg]

Completely untested:

Sub OpenFilesInALocation()
    Const sPath     As String = "C:\Users\Wil Moosa\Desktop\Updater\Vakantiekaart\"
    Dim sFile       As String
 
    Dim wkb         As Workbook
    Dim wks1        As Worksheet
    Dim wks2        As Worksheet
 
    sFile = Dir(sPath & "*.xls")
 
    With New Collection
        Do While Len(sFile)
            .Add sPath & sFile
            sFile = Dir()
        Loop
 
        Do While .Count
            Set wkb = Workbooks.Open(.Item(1))
            .Remove 1
 
            ' Wow, this is confusing ...
            Set wks1 = wkb.Worksheets(1)    ' the first worksheet
            Set wks2 = wkb.Worksheets("1")  ' the worksheet named "1"
 
            wks2.Protect UserInterfaceOnly:=True
            wks2.Range("D11").FormulaR1C1 = "'" * wks1.Name & "'!R[7]C + " & _
                                            "'" * wks1.Name & "'!R[9]C"
            wkb.Close SaveChanges:=True
        Loop
    End With
End Sub

 

[Wil Moosa]

shg, I did not get your code to work but jmthompson your code worked well (after I figured out what I was doing wrong with the path).

Thank you both...