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

 

[DanteAmor]

Can you put the complete code here to obtain a data from the book with your suggestion?

@johnnyL We are waiting for your code ...

 

[DanteAmor]

.deleted

 

[johnnyL]

Here is my result from testing your code.

It gives the right result, but like I said, the workbook that was supposed to be closed shows up in the panel after execution of the code.

 

[DanteAmor]

that was supposed to be closed

Okay, the book is activated (open), now, you could put your suggested code. Or are you just going to throw away my suggestion.

Here's a thread, in post #16, with the code to get the sheet names of a closed workbook using ADODB.Connection:

Get worksheet names from closed workbook

Hello, Does anyone have VBA code that will get all worksheet names from a closed workbook. Thank you in advance. Aloha, Rey

www.mrexcel.com

 

[johnnyL]

@DanteAmor , I actually wanted your code to work as requested to save me time from putting together something that might work. Any code I suggest will be from googling and making some alterations possibly. I did see that post earlier today that you just mentioned, I think I have found some better code, but I will have to test it.

I certainly did not want you to feel like you were being attacked in any way. You often come up with some amazing code on this site. You said your code worked as requested so I honestly thought that I was misunderstanding something. No offense was intended @DanteAmor Game ON!!!

 

[DanteAmor]

get from a closed workbook file a cell value eg B1, when you do not know the name of the only existing sheet

Try this for excel 2007.
If you test it with a version other than 2007 you will have to change the "Provider=Microsoft..." instruction to the corresponding one.

Finds the name of the first sheet of the closed workbook and gets the data from the cell.

Sub getCellValue_1()
  Dim wTxT As String, wGetValue  As String
  Dim wPath As String, wFile As String, wSHT As String, wCell As String
  Dim conexion As Object, objCat As Object, tbl As Variant
  
  wPath = "C:\trabajo\"
  wFile = "libro4.xlsx"
  wCell = "B1"
  
  Set conexion = CreateObject("adodb.connection")
  conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
    wPath & wFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
  Set objCat = CreateObject("ADOX.Catalog")
  Set objCat.ActiveConnection = conexion
  
  For Each tbl In objCat.Tables
    wSHT = Replace(tbl.Name, "$", "")
    Exit For
  Next tbl
  
  If wSHT <> "" Then
    wTxT = "'" & wPath & "[" & wFile & "]" & wSHT & "'!" & Range(wCell).Address(, , xlR1C1)
    wGetValue = ExecuteExcel4Macro(wTxT)
    MsgBox wGetValue
  End If
End Sub

 

[johnnyL]

Sub DanteAmorGetCellValues_2()
'
'   This script will ask the user for an excel file name that is closed.
'       It will then find each sheet name in the selected (closed) file and
'       display to the debug (Immediate) window the contents of a particular cell
'       in each sheet.
'
'
    Dim wTxT        As String, wGetValue    As String
    Dim wPath       As String, wFile        As String, wSHT As String, wCell    As String
'
    Dim conexion    As Object, objCat       As Object
'
    Dim tbl         As Variant, UserFile    As Variant
'
    wCell = "B1"
'
    UserFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*")
'
    If UserFile = False Then Exit Sub                                           ' Exit if User cancels
'
    wPath = Left$(UserFile, InStrRev(UserFile, "\"))
    wFile = Mid$(UserFile, InStrRev(UserFile, "\") + 1)
'
    Set conexion = CreateObject("adodb.connection")
'
    conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & wPath & wFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
'
    Set objCat = CreateObject("ADOX.Catalog")
    Set objCat.ActiveConnection = conexion
'
    For Each tbl In objCat.Tables
        wSHT = Replace(tbl.Name, "$", "")
'
        If wSHT <> "" Then                                                                          ' sheet name found
            wTxT = "'" & wPath & "[" & wFile & "]" & wSHT & "'!" & Range(wCell).Address(, , xlR1C1)
            wGetValue = ExecuteExcel4Macro(wTxT)
'
            Debug.Print "The Sheet Named " & "'" & wSHT & "' in " & wPath & wFile & " has a " & wCell & " cell value of = " & wGetValue
        End If
    Next
End Sub

This code builds upon @DanteAmor's previous code here so that it displays each sheet name from a closed workbook as well as the contents of a predetermined cell in each sheet. I have tested this against files that are sheet protected with a password as well as 'protect workbook' password protected.

 

[johnnyL]

Here it is prettied up a bit:

Sub DanteAmorGetCellValues_3()
'
'   This script will ask the user for an excel file name that is closed.
'       It will then find each sheet name in the selected (closed) file and
'       display to the debug (Immediate) window the contents of a particular cell
'       in each sheet.
'
    Dim RowCounter  As Long
'
    Dim wTxT        As String, wGetValue    As String
    Dim wPath       As String, wFile        As String, wSHT As String, wCell    As String
'
    Dim conexion    As Object, objCat       As Object
'
    Dim tbl         As Variant, UserFile    As Variant
'
    wCell = "B1"                                                                ' <--- Set this to the cell that you want
    Range("C1") = wCell & " Cell Value"
'
    Range("A2:C30").ClearContents
'
    UserFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*")
'
    If UserFile = False Then Exit Sub                                           ' Exit if User cancels
'
    wPath = Left$(UserFile, InStrRev(UserFile, "\"))
    wFile = Mid$(UserFile, InStrRev(UserFile, "\") + 1)
'
    Set conexion = CreateObject("adodb.connection")
'
    conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & wPath & wFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
'
    Set objCat = CreateObject("ADOX.Catalog")
    Set objCat.ActiveConnection = conexion
'
    RowCounter = 1
'
    For Each tbl In objCat.Tables
        wSHT = Replace(tbl.Name, "$", "")
'
        If wSHT <> "" Then                                                                          ' sheet name found
            RowCounter = RowCounter + 1
'
            wTxT = "'" & wPath & "[" & wFile & "]" & wSHT & "'!" & Range(wCell).Address(, , xlR1C1)
            wGetValue = ExecuteExcel4Macro(wTxT)
            If wGetValue = "0" Then wGetValue = "Empty Cell or zero value"
'
            Range("A" & RowCounter) = wPath & wFile
            Range("B" & RowCounter) = wSHT
            Range("C" & RowCounter) = wGetValue
        End If
    Next
    MsgBox "Search Completed!"
End Sub

BTW, Tested in Excel 2007 32Bit & Excel 2013 32Bit.

 

[drom]

Use GetObject to extract data:

For example:

Sub GetCellValue()
  Dim wTxT As String, wPath As String, wFile As String, wCell As String
 
  wPath = "C:\data\"
  wFile = "book.xlsx"
  wCell = "B1"
 
  wTxT = GetObject(wPath & wFile).Sheets(1).Range(wCell).Value
End Sub

Hi and thanks!
I was using this solution, I thought there was another alternative

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.