getting data from closed workbook

[keyur]

hi

i am using the second code on this page to get data from a
closed workbook

http://www.exceltip.com/st/Import_data_from_a_closed_workbo
ok_(ADO)_using_VBA_in_Microsoft_Excel/429.html

i entered the file location in the tester module. i dont
see anything else that needs to be change. i skips to the
message. and then gives me an error Type mismatched at
For r = LBound(tArray, 1) To UBound(tArray, 1)

can someone help me get throught this please.
thanks

 

[John Williams]

I've used the code you refer to and it works fine. Check very
carefully your filename in the line:

tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls",
"A1:B21")

The first argument must obviously refer to an Excel workbook on your
machine. If I give the wrong filename or folder path I get the
invalid file prompt followed by the type mismatch error.

If your workbook is in My Documents, it can be confusing to know the
precise folder path. The easiest way to determine this is using
Windows Explorer with the full path displayed in the address bar
(Tools > Folder Options > View tab > check Display the full path in
the address bar).

 

[onedaywhen]

First, this code is famous in the Excel community and originates from:

http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

Second, I just tried it as you suggested (i.e. enter a random file
path) and I too got an error, 'Subscript out of range' because the
recordset only contained only one row so the resulting array has only
one dimension (the code expects two dimensions).

To be fair, I think the code is intended to demonstrate ADO with Excel
rather than provide a workable solution in all circumstances. It
doesn't contain much validation or error handling so it replies on the
user supplying sensible values. GIGO, as we say.

Have a look at your range, the one at which you've pointed the code.
Is it laid out as a database i.e. rows of columns (preferably more
than one of each)? If you're still having problems, post back with
your data.

 

[onedaywhen]

First, this code is famous in the Excel community and originates from:

http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

Second, I just tried it as you suggested (i.e. enter a random file
path) and I too got an error, 'Subscript out of range' because the
recordset only contained only one row so the resulting array has only
one dimension (the code expects two dimensions).

To be fair, I think the code is intended to demonstrate ADO with Excel
rather than provide a workable solution in all circumstances. It
doesn't contain much validation or error handling so it replies on the
user supplying sensible values. GIGO, as we say.

Have a look at your range, the one at which you've pointed the code.
Is it laid out as a database i.e. rows of columns (preferably more
than one of each)? If you're still having problems, post back with
your data.