Link to specific Excell cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to do a Link table (not import table) Access to an Excell
spreadsheet that allows me to link to a specific worksheet and to either
specific cells or to all cells starting at a specific point. I have a
co-worker who has years worth of data that exists on a few spreadsheets. He
has them grouped together on worksheets, but none start at the first row
(maybe fifth due to titles on each worksheet). Can you show both ways; one
starting at a certain row on a certain worksheet, and one starting and ending
at specific rows? A Link works better since he adds info daily, and he
doesn't want to redo all his sheets, due to pivot charts/graphs and such.

your help will be greatly appreciated.
 
Hi Jackle,

Create a new query. Don't select any tables. Switch the query into SQL
view, and type in a SQL statement along these lines:


SELECT *
FROM
[Excel 8.0;HDR=Yes;database=C:\folder\file.xls;].[Sheet1$C10:G100]
;

SELECT F1 AS MyField, F2 AS MyOtherField, F3 AS AThirdField
FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$C10:G100]
;

As far as I know you need to specify either the entire sheet [Sheet1], a
range of cells as above [Sheet2$A5:F50000] (just set the bottom of the
range below the maximum number of records you expect, blank rows at the
bottom are ignored), or a named range [NameOfRange].
 
John said:
As far as I know you need to specify either the entire sheet [Sheet1], a
range of cells as above [Sheet2$A5:F50000] (just set the bottom of the
range below the maximum number of records you expect, blank rows at the
bottom are ignored), or a named range [NameOfRange].
Click to expand...

A couple of variations. It could be a worksheet-level defined Name
(named range) e.g. [Sheet2$NameOfRange]. Another possibility is
querying a range on the first worksheet, even if it's not visible,
without knowing its name e.g. [A:D].

Jamie.

--
 
I guess this linking could be done with a macro? Or how should it be done?
I've only done it with the wizard, and have not seen any way to specify
ranges.
thanks,
jackle.
"onedaywhen" wrote:
Click to expand...
John said:
As far as I know you need to specify either the entire sheet [Sheet1], a
range of cells as above [Sheet2$A5:F50000] (just set the bottom of the
range below the maximum number of records you expect, blank rows at the
bottom are ignored), or a named range [NameOfRange].
Click to expand...

A couple of variations. It could be a worksheet-level defined Name
(named range) e.g. [Sheet2$NameOfRange]. Another possibility is
querying a range on the first worksheet, even if it's not visible,
without knowing its name e.g. [A:D].

Jamie.
Click to expand...
 
Queries like the ones I suggested (having learnt from Jamie how useful
they are) are basically the same thing as linked tables. Usually, the
biggest practical difference is that they are listed among Queries
rather than Tables.

You can create them manually by typing an SQL statement into a new
query, as I described in my first post.

Alternatively it's possible to create them under program control by
writing VBA code that assembles the appropriate SQL - but first you
would need to have had some practice with SQL.

I guess this linking could be done with a macro? Or how should it be done?
I've only done it with the wizard, and have not seen any way to specify
ranges.
thanks,
jackle.
"onedaywhen" wrote:
Click to expand...
John said:
As far as I know you need to specify either the entire sheet [Sheet1], a
range of cells as above [Sheet2$A5:F50000] (just set the bottom of the
range below the maximum number of records you expect, blank rows at the
bottom are ignored), or a named range [NameOfRange].
Click to expand...

A couple of variations. It could be a worksheet-level defined Name
(named range) e.g. [Sheet2$NameOfRange]. Another possibility is
querying a range on the first worksheet, even if it's not visible,
without knowing its name e.g. [A:D].

Jamie.
Click to expand...
Click to expand...
 
Back
Top