Hi Gary

Application.GetOpenFilename will use the default folder

You can change it like this and turn it back at the end of the macro
Here a small example

Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then
Set wb = Workbooks.Open(FName)
MsgBox "your code"
wb.Close
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub


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


"Gary Keramidas" wrote in message ...
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
=============================

Hi Ron, It seems like when I select the B2 cell this routins still opens up
the target file directory to the last one used before positioned below. I am
hoping for it to open up the sub-rectory "\RaceData-XLS-Ready"
Any clues?
Here is the code:
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim racePark As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String


Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

racePark = Left(srcProgramDataInputWs.Range("H3").Value, 3)

If Target.Address = "$A$1" Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path & "/RaceData-XLS-Ready"
ChDrive MyPath
ChDir MyPath

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim raceParkList As Variant
Dim src As Variant

i = 6
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
Do Until raceParkList = ""
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
If racePark = raceParkList Then NewBettingWsTabColor =
srcProgramDataInputWs.Range("O" & i).Value
i = i + 1
Loop

Range("N3").Select

srcBettingTemplateWs.Copy befo=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j * 12)
+ 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If


If Target.Address = "$E$1" Then
If MsgBox("Are you sure you want to CLEAR this Worksheet?", _
vbYesNo) = vbYes Then
ActiveSheet.Unprotect
ActiveSheet.Range("N3:Q242").Formula = _
srcProgramSummaryTemplateWs.Range("N3:Q242").Formu la
ActiveSheet.Protect
Range("K1").Value = "default"
Range("N3").Select
End If
End If


If Target.Address = "$B$1" Then
Dim SelectedTxtInputFile As Variant
SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)

If SelectedTxtInputFile = "False" Then
'Range("N3").Select
Else

srcProgramDataInputWs.Unprotect
srcProgramDataInputWs.Range("A3:H242").ClearConten ts

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H242" ))
.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
srcProgramDataInputWs.Protect
End If
Range("N3").Select
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'add your code here
Range("K1").Value = "Clear"
ws_exit:
Application.EnableEvents = True
End Sub