Fetching External Data from Excel
 

Hello All,

So far I jave used External Data option in Excel to import
data from 2 sources..Access and MS SQL server..now i want
to import the data from an another Excel file..

I have created one excel file similar to my access table
with 5 columns with proper headings and some data inside
it.(Assuming that the excel will treat this as a kind of
table!!).

Now when i tried to import the same from another Excel
file, it says "The data source contains no visible tables"

Can anyone help on this on how to fetch the data using
this option.

My second Query related to the prev one..

ALternatively, can i use Data objects like DAO,ADO... in
VBA macros to connect to the Excel file(where i have
stored the data) and play with the data..

I have used the same technique to read Access,SQL tables..

Any help would be highly appreciated..

Wish you a wonderful new year 2005!!

Thanks

Sri

Hi

You must define your source table as non-dynamic named range before, with
header row included (and save the workbook after this)
P.e. you define a named range
MyRange=Sheet1!$A$1:$E$1000
and save the workbook. Now when you try to create a query, the range MyRange
is displayed in query wizard as table. Header values in table are
interpreted as field names.
The source table can be in separate workbook, or in same workbook with
query.


Arvi Laanemets


"Sri" wrote in message
...
Hello All,

So far I jave used External Data option in Excel to import
data from 2 sources..Access and MS SQL server..now i want
to import the data from an another Excel file..

I have created one excel file similar to my access table
with 5 columns with proper headings and some data inside
it.(Assuming that the excel will treat this as a kind of
table!!).

Now when i tried to import the same from another Excel
file, it says "The data source contains no visible tables"

Can anyone help on this on how to fetch the data using
this option.

My second Query related to the prev one..

ALternatively, can i use Data objects like DAO,ADO... in
VBA macros to connect to the Excel file(where i have
stored the data) and play with the data..

I have used the same technique to read Access,SQL tables..

Any help would be highly appreciated..

Wish you a wonderful new year 2005!!

Thanks

Sri

Arvi Laanemets wrote:

You must define your source table as non-dynamic named range

Or in the table options, check 'system tables' and the worksheet names
will be available. Or just type the SQL in, using the sheet name or
range address e.g.

SELECT MyCol FROM [Sheet1$];

Jamie.

--