macro that reads data from a closed workbook

[veggiesaregood]

I am trying to write a macro that can read data from a closed workbook
and use this data to fill a listbox in a userform. I am having trouble
with reading the data from the closed workbook. I know on a PC I can
use the ActiveX Data Objects library and use ADODB.connection and
ADODB.recordset. However, I am using a MAC where the ActiveX Data
Objects library is unavailable. Can someone please suggest an
alternative way for me to get the data from the closed workbook.

Thanks!

 

[JE McGimpsey]

I am trying to write a macro that can read data from a closed workbook
and use this data to fill a listbox in a userform. I am having trouble
with reading the data from the closed workbook. I know on a PC I can
use the ActiveX Data Objects library and use ADODB.connection and
ADODB.recordset. However, I am using a MAC where the ActiveX Data
Objects library is unavailable. Can someone please suggest an
alternative way for me to get the data from the closed workbook.

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:

Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel/tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String

sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Substitute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True, xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function

Note that it uses an XL4M command. It cannot be used from the worksheet,
but works fine when called by a macro.