Any Idea on how to modify this code relating to query???
 

Dear All

Please refer to the below code... Can anyone show me how I can modif
the code so that I can get the file directory (in bold) to be referre
to a specified cell? For eg, the text in Sheet1 Cell A1 i
F:\work\job.dat. Hence, the macro should take the value of Cell A1 an
then continue with its processes... One problem I encounter is that i
says that the "Destination" should be the same as where the specifie
cell is but I want the destination to be say Sheet2. I would reall
appreciate any suggestion here...

Cheers

Sub test2()

Range("A1").Select
Wit
ActiveSheet.QueryTables.Add(Connection:="TEXT;*F:\ work\job.dat*", _
Destination:=Sheets("Sheet3").Range("A1"))
.Name = "job"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet3").Select
End Su

--
hc
-----------------------------------------------------------------------
hce's Profile: http://www.excelforum.com/member.php...nfo&userid=351
View this thread: http://www.excelforum.com/showthread.php?threadid=26913

Any Idea on how to modify this code relating to query???
 

Sub test2()
Dim fname As String

fname = Sheets("Sheet1").Range("A1").Value 'stores the file name
Sheets("Sheet3").Select 'assumes sheet3 exists already

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;*& fname &*", _
Destination:=Range("A1"))
Name = "job"
.....Rest of query

Mike F
"hce" wrote in message
...

Dear All

Please refer to the below code... Can anyone show me how I can modify
the code so that I can get the file directory (in bold) to be referred
to a specified cell? For eg, the text in Sheet1 Cell A1 is
F:\work\job.dat. Hence, the macro should take the value of Cell A1 and
then continue with its processes... One problem I encounter is that it
says that the "Destination" should be the same as where the specified
cell is but I want the destination to be say Sheet2. I would really
appreciate any suggestion here...

Cheers

Sub test2()

Range("A1").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;*F:\ work\job.dat*", _
Destination:=Sheets("Sheet3").Range("A1"))
Name = "job"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
RefreshStyle = xlInsertDeleteCells
SavePassword = False
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
TextFilePromptOnRefresh = False
TextFilePlatform = 850
TextFileStartRow = 1
TextFileParseType = xlDelimited
TextFileTextQualifier = xlTextQualifierDoubleQuote
TextFileConsecutiveDelimiter = False
TextFileTabDelimiter = True
TextFileSemicolonDelimiter = False
TextFileCommaDelimiter = True
TextFileSpaceDelimiter = False
TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
TextFileTrailingMinusNumbers = True
Refresh BackgroundQuery:=False
End With
Sheets("Sheet3").Select
End Sub


--
hce
------------------------------------------------------------------------
hce's Profile:
http://www.excelforum.com/member.php...fo&userid=3518
View this thread: http://www.excelforum.com/showthread...hreadid=269135