From a closed WKB get a cell Value when we do not know the Sheet Name

[drom]

Hi and thanks in advance!
Is it possible to get from a closed workbook file a cell value eg B1, when you do not know the name of the only existing sheet within the desired workbook
I do not want to open the WKB (I know how to get the value opening the file)

I am trying to use Walkenbach's GetValue(path, file, sheet, ref) using ExecuteExcel4Macro but in my case

When using Function GetValue(wPath, wFile, wSheet, wRef)

I know:

• wPath Known
• wFile Known
• wSheet NOT Known
• wRef Known

So in this case I do not know if is not Possibble when the sheet name in unknown

• If NOT, then I will open the file, to get the value

Thanks

 

[johnnyL]

Hi and thanks in advance!
Is it possible to get from a closed workbook file a cell value eg B1, when you do not know the name of the only existing sheet within the desired workbook
I do not want to open the WKB (I know how to get the value opening the file)

I just want to point out that the code listed in this thread works in the cases that only one worksheet is in the closed workbook.

This code could fail to find the first worksheet tab in a closed workbook that has multiple worksheet tabs. The reason for the potential failure is the result returned is from the worksheet in alphabetical order. For example if you have your worksheet tabs arranged from left to right, Sheet2, Sheet3, Sheet1, The code previously listed here will return a result from Sheet1, Not the 'first sheet' which would be Sheet2 in this example.

Just an FYI!

 

[johnnyL]

A simple excel formula is all that is needed to list the sheet names from a closed workbook in the Tab order.

='C:\ExcelFilePath\[ExcelFileName.ExcelFileExtention]'!B1

You could then select the sheet name and it will result in the value from B1, in this case, in that selected sheet name

Some VBA code to do this:

Sub FormulaCodeToGetSheetNamesInTabOrderThenCellValueFromSelectedSheet()
'
'   Excel cell formula to accomplish task:
'       ='C:\ExcelFilePath\[ExcelFileName.ExcelFileExtention]'!B1
'
    CellToSaveResultIn = "A1"                                                           ' <--- Set this to desired destination address of cell value found
    CellToGetValueFromInTheClosedWorkbook = "B1"                                        ' <--- Set this to desired source address in closed workbook
'
    UserFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*")    ' Have user select the closed workbook to use
'
    If UserFile = False Then Exit Sub                                                   ' Exit if User cancels
'
    wbPath = Left$(UserFile, InStrRev(UserFile, "\"))                                   ' Get Path Name of selected file
    wbName = Mid$(UserFile, InStrRev(UserFile, "\") + 1)                                ' Get File Name of selected file

    On Error Resume Next                                                                ' Error handler added to handle case that user selects cancel
    Sheets("Sheet1").Range(CellToSaveResultIn).Formula = "='wbPath[wbName]'!" & CellToGetValueFromInTheClosedWorkbook & ""
    Err.Clear
'
    If Sheets("Sheet1").Range(CellToSaveResultIn) = 0 Then Sheets("Sheet1").Range(CellToSaveResultIn) = "Empty Cell or a value of Zero found"
End Sub

I think I am done playing with this now.

 

[johnnyL]

Ok one more version:

Sub FormulaCodeToGetSheetNamesInTabOrderThenCellValueFromSelectedSheetV2()
'
'   Excel cell formula to accomplish task:
'       ='C:\ExcelFilePath\[ExcelFileName.ExcelFileExtention]'!B1
'
    CellToSaveResultIn = "A1"                                                           ' <--- Set this to desired destination address of cell value found
    CellToGetValueFromInTheClosedWorkbook = "B1"                                        ' <--- Set this to desired source address in closed workbook
'
    On Error Resume Next                                                                ' Error handler added to handle case that user selects cancel
    Sheets("Sheet1").Range(CellToSaveResultIn).Formula = "='C:\ExcelFilePath\[ExcelFileName.ExcelFileExtention]'!" & CellToGetValueFromInTheClosedWorkbook & ""
    Err.Clear
'
    If Sheets("Sheet1").Range(CellToSaveResultIn) = 0 Then Sheets("Sheet1").Range(CellToSaveResultIn) = "Empty Cell or a value of Zero found"
End Sub

 

[johnnyL]

I can't believe that nobody has responded to my last few posts here. I thought the codes were borderline genius in that they prompt the user for a file, even though the code doesn't indicate that. No ADO, DAO, etc code needed to get the closed workbook sheet names. I guess people aren't that impressed these days. A formula and my last post that indirectly ask user for file? No big deal apparently. Tough crowd!