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