How about pointing and clicking on the file:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub SetUNCPath(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme()
Dim myFileName As Variant
Dim myFolder As String

myFolder = CurDir

On Error Resume Next
SetUNCPath "\\scdff001\pdrive\REMARKETING\" _
& "Weekly Reports\LM273(AgingOfInv)\Text Files"
If Err.Number < 0 Then
MsgBox "Design Error, but keep going"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename("Text Files,*.txt")

SetUNCPath myFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), _
Array(15, 1), Array(29, 1), Array(46, 9), _
Array(64, 1), Array(66, 1), Array(76, 9), _
Array(128, 1)), TrailingMinusNumbers:=True

End Sub

The API stuff allows you to change to that UNC folder (ChDrive and ChDir won't
work with that kind of stuff.)

(And watch the spelling your path. You were hit with linewrap and I guessed at
what it should be.

mk389 wrote:

Hi,

Right now I have

ChDir _
"\\scdff001\pdrive\REMARKETING\Weekly
Reports\LM273(AgingOfInv)\Text Files"
Workbooks.OpenText Filename:= _
"\\scdff001\pdrive\REMARKETING\Weekly
Reports\LM273(AgingOfInv)\Text Files\LM273 111304.TXT" & "" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(Array(0, 1), Array(13, 1), Array(15, 1), Array(29, 1),
Array(46, 9), Array(64, 1), _
Array(66, 1), Array(76, 9), Array(128, 1)),
TrailingMinusNumbers:=True

That imports a text file and arranges them in a rows and deletes
information I dont want.

What I want to do is modfiy the file name with a input box.

How would I do that.

Thanks,
mk

--
mk389
------------------------------------------------------------------------
mk389's Profile: http://www.excelforum.com/member.php...o&userid=15272
View this thread: http://www.excelforum.com/showthread...hreadid=318811

--

Dave Peterson