[AccessD] Syntax for seeing named range in excel

Hi John

Yes, it is possible to update a linked xls sheet/range - except if you have applied the latest servicepack. 

A Named Range can be created like this:

  ActiveWorkbook.Names.Add "John", "=$C1:$D4" 

or more specific:

  ActiveWorkbook.Names.Add "John", "=NameOfWorksheet!$C1:$D4" 

If that Named Range exists, it will be adjusted to the new range settings.

/gustav

>>> jwcolby at ColbyConsulting.com 19-01-2006 19:53 >>>
OK oh hero, now is it possible to update the named range through a query?  I
tried saving the working select query, then opening that and modifying the
data but that definitely does not work.  Is this part of the "patent
lawsuit" changes or am I just doing something wrong?  

If a "Update" syntax would work that would be fine.  I am running code
anyway, not manually updating the data as I was trying to do in the test
above.

As I mentioned, I have a template with the named ranges all set up.  I would
like to be able to update that spreadsheet (append data into it) rather than
have to build up a new spreadsheet.  I can create the spreadsheet but then I
would need the syntax to create named ranges so I can get the data later -
using the syntax I just got from you.


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, January 19, 2006 12:44 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Syntax for seeing named range in excel

Hi John

You miss the Excel header. Two options exist:

 SELECT
    NameOfField1,
    NameOfField2
  FROM
    [Range] AS T IN '' [Excel 5.0;DATABASE=c:\windows\temp\some.xls;];

In the second syntax the trick is the brackets. They can be omitted in this
example but are mandatory if you deal with filenames containing spaces.

This should be changed to:

  SELECT
    NameOfFiels1,
    NameOfField2
  FROM
    [Excel 5.0;DATABASE=c:\windows\temp\some.xls;HDR=YES].[Range] AS T;


>>> jwcolby at ColbyConsulting.com 19-01-2006 18:14:42 >>>
Does anyone know the syntax in an SQL statement for seeing a named range in
an excel spreadsheet?

I have a spreadsheet DocumentRequestTemplate.xls, which has a sheet
xlsReqForDocuments which has a named range Sec_DocWithHdr, Sec_DocNoHdr etc.

I assume that the syntax would look something like 

SELECT * from SEC_DocWithHdr IN DocumentRequestTemplate.xls

Doing this gets me "unrecognized database format" however.

Anyone know what the real answer is?

John W. Colby
www.ColbyConsulting.com