You can make the FName argument optional and, if omitted, default to a
predetermined value. I think you will have to change the order of your
arguments so that the required argument precedes the optional argument (or
you can make Sep optional as well and not set a default value for it-then you
can leave the order alone).

On a related note - you can also use IsMissing as a test to see if the
optional argument was provided.

See VBA help for more info on optional and named arguments.


Public Sub ImportTextFile(Sep As String, Optional FName As String =
"C:\Excel")


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub