I have a worksheet "Setup" where users type in a date in a cell named
"ChtDte" and a path and database name in a cell named "FLName". I am using
DOA to connect to a database and return a record set. The query used
"qryCOCostwRates" uses a date paramater. Because this sheet will be used by
several users all pointing to the database in different locations, I need to
know where they have the database.

I need to get the values in these two range names in the setup tab of the
spreadsheet so I can connect to the data, and provide a value for the
paramiter. This should be easy but I can't seem to find the solution. Any
ideas? Here's what I have tried:

Dim xlwsSetup As Excel.Worksheet
Dim xlrngFl As Excel.Range
Dim xlrngDte As Excel.Range
Dim xlWb As Excel.Workbook
Dim dbFln as String
Dim RptDte as Date
Set xlWb = ActiveWorkbook
Set xlwsSetup = xlWb.Worksheets("Sheet3")
Set xlwsSetup = ActiveSheet
Set xlrngFl = xlwsSetup.Range("FlName")

'The above is cell C3 but it may change so I named the range "FLName". The
user types "C:\Data\db\mydb.mdb" into the cell.

Set xlrngDte = xlwsSetup.Range("ChtDte")
'The above is cell C2 and it has a date in it like 2/28/09. If rows or
columns are inserted, I don't want to lose the reference so it's named ChtDte.

dbFln = xlrngFl.Value
RptDte = xlrngDte.Value

Is the file path and name (FLName) and the report date (ChtDte) now in my
variables?


--
Thanks in advance!
**John**

I got it worked out... Thanks for all the help...
--
Thanks in advance!
**John**


"John" wrote:

> I have a worksheet "Setup" where users type in a date in a cell named
> "ChtDte" and a path and database name in a cell named "FLName". I am using
> DOA to connect to a database and return a record set. The query used
> "qryCOCostwRates" uses a date paramater. Because this sheet will be used by
> several users all pointing to the database in different locations, I need to
> know where they have the database.
>
> I need to get the values in these two range names in the setup tab of the
> spreadsheet so I can connect to the data, and provide a value for the
> paramiter. This should be easy but I can't seem to find the solution. Any
> ideas? Here's what I have tried:
>
> Dim xlwsSetup As Excel.Worksheet
> Dim xlrngFl As Excel.Range
> Dim xlrngDte As Excel.Range
> Dim xlWb As Excel.Workbook
> Dim dbFln as String
> Dim RptDte as Date
> Set xlWb = ActiveWorkbook
> Set xlwsSetup = xlWb.Worksheets("Sheet3")
> Set xlwsSetup = ActiveSheet
> Set xlrngFl = xlwsSetup.Range("FlName")
>
> 'The above is cell C3 but it may change so I named the range "FLName". The
> user types "C:\Data\db\mydb.mdb" into the cell.
>
> Set xlrngDte = xlwsSetup.Range("ChtDte")
> 'The above is cell C2 and it has a date in it like 2/28/09. If rows or
> columns are inserted, I don't want to lose the reference so it's named ChtDte.
>
> dbFln = xlrngFl.Value
> RptDte = xlrngDte.Value
>
> Is the file path and name (FLName) and the report date (ChtDte) now in my
> variables?
>
>
> --
> Thanks in advance!
> **John**