Display contents of a worksheet in a listbox without opening it

[sunilbsrv2k]

Hi,

I have a piece of code with which I can display the required range or a worksheet in a Listbox.

However, for some audit purpose, I want the contents of a workbook to be displayed in the list box without actually opening it.

Is this a possibility? Please advise.

Below is my code for displaying certain cells in the current workbook.

Private Sub CommandButton1_Click()[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]   With ListBox1[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]    .ColumnCount = 6[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]    .ColumnWidths = "50;70;90;70;70;50"[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]    .RowSource = Sheet1.Range("A1:F20").Address[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]   End With[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.7)][FONT="Source Serif Pro"]End Sub

Thanks
[/FONT][/COLOR]

 

[jproffer]

This function will pull data from closed workbooks:

Private Function GetValue(path, file, sheet, ref)
Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If


arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)

End Function

ref is the cell reference...the rest are pretty self explanatory. The "arg" string has to be EXACTLY like that. This was found (probably found by someone on this site, on my behalf) online. I wish I could give proper credit to either the original creator or the person who helped me with it...or both, but my memory just isn't what it used to be

EDIT: Or did you mean without opening the workSHEET? If that's the case, you don't need this function at all.

ListBox1.AddItem (Sheets("ShName").Range("A1").Value)

 

[sunilbsrv2k]

Hi ... Thanks for your reply.

Yes my question was to get data from unopened workbooks.

You mentioned the code: ListBox1.AddItem (Sheets("ShName").Range("A1").Value)

But how to include path & <filename> in this?

Thanks

 

[jproffer]

Nope...don't worry about that code if the workbook is closed.

The function I posted above it will pull data from a closed workbook...

You could use it like:

p = "C:\WbPath\" 'See below for more info.
f = "MyWorkbookName.xlsx"
s = "WsName"  'NOT like normal coding, this is just a string of the name only.  In other words not this:  Sheets("WsName")
a = "A1" 'Again, not like normal coding...not Range("A1")...just "A1"

ListBox1.AddItem GetValue(p,f,s,a)

You can use this to get the exact path of the workbook. In a module that's IN the workbook you want to pull FROM....put this:

Sub test()
MsgBox ThisWorkbook.path
End Sub

and run it.

One note: The Excel4Macro is slow, so if you have very many items to pull, you may want to just open the workbook behind the scenes (minimized or whatever), pull your data, then close it again.

 

[sunilbsrv2k]

Hi jproffer,

Perfect... That's working fine.. Thanks much

Regarding this, I have couple of questions:

1. Excel4Macro is it a inbuilt macro?

2. This is giving info of one cell. What if I want a range/ table to be displayed. I tried giving it like "B6: D9" but its not working.

Thanks

 

[sunilbsrv2k]

Hi

This is working fine. Thanks for your help.

However, I have two questions:

1. Excel4Macro --- Is this a Microsoft built in macro?

2. This code gives me the data of only one cell. Is it possible to display an entire row or set of rows?

Thanks

 

[jproffer]

1) I'm not sure to be honest. I just recently discovered it myself.

2) I've never tried to pull more than one cell at a time. I suppose you could use a loop and "GetValue" for each iteration of "i" (or whatever variable). Maybe something like:

p = "C:\WbPath\" 'See below for more info.
f = "MyWorkbookName.xlsx"
s = "WsName"  'NOT like normal coding, this is just a string of the name only.  In other words not this:  Sheets("WsName")

For i = 1 to 10
     a = "A" & i
     ListBox1.AddItem GetValue(p,f,s,a)
Next i

That would give you A1 thru A10, but it would be one cell at a time...so if you need 10,000 cells instead of 10 it would be very slow, like I said above.

There may be a way to pass an entire array to the function at once as the variable "a", but that's beyond my knowledge at the moment. Hopefully someone else will come along and school both of us whether it's possible or not.