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

drom

Active Member
Joined
Mar 20, 2005
Messages
450
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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 (y)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
 

Attachments

  • Dante1.PNG
    Dante1.PNG
    17 KB · Views: 5
  • Dante2.PNG
    Dante2.PNG
    26.4 KB · Views: 5

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,303
Office Version
  1. 2007
Platform
  1. Windows
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:

 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Dec 3, 2018
Messages
13,303
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Here it is prettied up a bit:

VBA Code:
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.
 

Attachments

  • Dante3.PNG
    Dante3.PNG
    7.8 KB · Views: 2
Last edited:

drom

Active Member
Joined
Mar 20, 2005
Messages
450
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Use GetObject to extract data:

For example:

VBA Code:
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 (y)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,303
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,136,771
Messages
5,677,635
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top