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

Since PathName is a variable, you need to concatenate it to the rest of the
connection string using &. Enclose the string prior to the PathName variable
in parenthesis. Enclose the string after the PathName variable in
parenthesis. The way it is now, the string is using the literal "PathName"
in the string because it is enclosed within the parenthesis with the rest of
the string. Try this:

Source=" & PathName & ";Mode=Share Deny None;Extended Properties="""";Us" _

"Riddler" wrote:

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