box to enter file name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tonso - 05 Feb 2008 19:51 GMT

I am using Excel 2002 to import data from another program into a
spreadsheet, using the Text To columns feature. I have a macro that
works fine to go to the drive [K:/], and find the file, which is named
"mydata". is it possible to have a box appear that would enable a user
to name a different file, other than "mydata"? the macro starts off as
below.
As you can see, it is limited to the K dire3ctorry and the file name
"mydata". can i modify it to allow a differnt drive, and then allow a
different filename?
thank you

ChDir "K:\"
Workbooks.OpenText Filename:="K:\iedata.exp", Origin:=437,
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False,
Space:=False, _
Other:=True, OtherChar:="|", FieldInfo:=Array(1, 1),
TrailingMinusNumbers _
:=True

Reply to this Message

Dave Peterson - 05 Feb 2008 20:12 GMT

Option Explicit
Sub testme()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "K:\"

'check to see if that newfolder actually exists
TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

Workbooks.OpenText Filename:=myfilename, Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

> I am using Excel 2002 to import data from another program into a
> spreadsheet, using the Text To columns feature. I have a macro that
[quoted text clipped - 17 lines]
> TrailingMinusNumbers _
> :=True

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 05 Feb 2008 20:26 GMT

Oops.

Change this line:
myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
to
myFileName = Application.GetOpenFilename(filefilter:="Exp Files, *.exp")

To limit it to *.exp files.

> Option Explicit
> Sub testme()
[quoted text clipped - 63 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Tonso - 05 Feb 2008 20:42 GMT

> Option Explicit
> Sub testme()
[quoted text clipped - 65 lines]
>
> - Show quoted text -

Works like a charm! Thanks you so very much!
Siege

Reply to this Message