just a question:

doesn't it always look in the path the xl sheet is in for a file? i didn't
think you would have to explicitly define the path when it's in the same
folder.Other than for coding correctness)

i thought it looked in the default path and then the path the excel file is
in, then if it doesn't find it, it gives an error.

just wondering. because i don't know for sure

--


Gary


"Ron de Bruin" wrote in message
...
Hi CRayF

You can use
ActiveWorkbook.Path



--
Regards Ron de Bruin
http://www.rondebruin.nl


"CRayF" wrote in message
...
Below is a macro that I now have working. The text file I'm trying to
select
will always be in the same directory as the XLS itself. What I'm hoping
to do
is rather than have a hard coded in as "H:\XLS", I'd like to symbolic
this
directory to the same as the XLS. Is there a variable I can use that
knows
what directory the XLS is running out of and is there a way to use it
below?
thanks in advance for your help

=================
Sub ImportProgramData()

Dim file_name As Variant

Range("A3:G300").Select
Selection.ClearContents

Application.DefaultFilePath = "H:\XLS" 'Set default file path to root
file_name = Application.GetOpenFilename

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & file_name _
, Destination:=Range("A3:G300"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
ActiveWorkbook.Save

End Sub
=============================