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**