Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data query refresh problem with changing filenames
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data query refresh problem with changing filenames
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query to Ex Data refresh changes formulas | Excel Discussion (Misc queries) | |||
Data Query will not Refresh automatically | Excel Worksheet Functions | |||
Web Query Refresh problem | Excel Discussion (Misc queries) | |||
SQL Query - Ability to Refresh Data is Lost | Excel Programming | |||
Web query .Refresh BackgroundQuery:=False problem | Excel Programming |