Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sri
 
Posts: n/a
Default 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
  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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



  #3   Report Post  
onedaywhen
 
Posts: n/a
Default

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.

--

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting Data from Excel DavidM Charts and Charting in Excel 4 December 30th 04 03:31 PM
multiple or changing colours in a data table on an excel chart fo. Kerri Buxton Charts and Charting in Excel 2 December 23rd 04 08:39 PM
Layout/Formatting problems of Data Queries in Excel? Andrew Harding Excel Discussion (Misc queries) 0 December 23rd 04 04:51 AM
Refreshing Access data in Excel Jimmy G Excel Discussion (Misc queries) 0 December 21st 04 04:53 PM


All times are GMT +1. The time now is 09:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"