Copy data from another Excel from a specific Sheet

[Vagelisr]

Hi to all

I want to open a specific folder, find all Excel and from a specific Sheet take a parm cell value

The problem is i cant open the specific sheet and i need your help

All the others work without issues

The code (Function) i use to open the other excel and copy the specific cell is this

Function OpenExcelFile(PathFolderName) As String
    Dim objExcel As Object, objWorkbook As Object
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open(PathFolderName)

[I][B]    ActiveSheet.Name = TabName[/B][/I]

    OpenExcelFile = objExcel.ActiveSheet.Range(CellName)
    objWorkbook.Close False 'savechanges:=false
    Set objExcel = Nothing
    Set objWorkbook = Nothing
End Function

The Bold line is where i try to change to the sheet i want but of course is wrong

 

[Norie]

All that line of code is going to do is try to change the name of the active sheet in the workbook you've just opened.

What worksheet in the workbooks you are opening do you want to copy the value/values from?

 

[Vagelisr]

First of all thanks for your quick responce.

Yes i know this (i understand first time i run the code)

I want to open the "PathFolderName" (Path with excel the file)
and after this to open a specific sheet. with parameter name TabName

 

[Vagelisr]

I change the code to this one

Function OpenExcelFile(PathFolderName) As String
    Dim objExcel As Object, objWorkbook As Object
    Dim objWorksheet As Excel.Worksheet
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open(PathFolderName)
    Set objWorksheet = objWorkbook.Worksheets(TabName)
    OpenExcelFile = objExcel.ActiveSheet.Range(CellName)
    objWorkbook.Close False 'savechanges:=false
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
End Function

But again they did not open the sheet i want