I have a macro that refreshes a query to a file and returns all the
data in the file and puts it on a sheet. My problem is that the
directory name can change because of what computer it is installed on.
So I wanted to have the macro look to a specific cell on a "Setup"
sheet that would give the directory path for the data files.
I get the refresh to work fine when the path is hard coded into the
macro but when I use a variable that points to a cell it fails.

If you have any ideas on how to do this I would appreciate it.

Thanks
Scott

This code works:

With Range("Eclipse_DataBase_Query").QueryTable
.Connection = Array( _
"OLEDB;Provider=VFPOLEDB.1;Data Source=H:
\Projects and Teams\Macomb\DH roll formers to Macomb 2008\DH RF data
1-1-2008 to 3-18-2008;Mode=Share Deny None;Extended
Properties="""";Us" _
, _
"er ID="""";Mask Password=False;Cache
Authentication=False;Encrypt Password=False;Collating
Sequence=MACHINE;DSN=""""" _
)
.CommandType = xlCmdTable
.CommandText = Array(DbName)
.Refresh BackgroundQuery:=False
End With


This is what I would like it to work like but get a error on.
Cell(1,1) on the setup sheet equals "H:\Projects and Teams\Macomb\DH
roll formers to Macomb 2008\DH RF data 1-1-2008 to 3-18-2008"


PathName=sheets("Setup").cells(1,1)

With Range("Eclipse_DataBase_Query").QueryTable
.Connection = Array( _
"OLEDB;Provider=VFPOLEDB.1;Data
Source=PathName;Mode=Share Deny None;Extended Properties="""";Us" _
, _
"er ID="""";Mask Password=False;Cache
Authentication=False;Encrypt Password=False;Collating
Sequence=MACHINE;DSN=""""" _
)
.CommandType = xlCmdTable
.CommandText = Array(DbName)
.Refresh BackgroundQuery:=False
End With