box to enter file name

T

Tonso

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
 
D

Dave Peterson

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
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
Click to expand...
 
D

Dave Peterson

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.

Dave said:
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
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
Click to expand...
Click to expand...
 
T

Tonso

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
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
Click to expand...
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
Click to expand...

--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Beginner Question 1
Problem opening text file... 5
Open Text Method Fails 7
saving a file using a cell for file name 2
range name as file name 3
Open delimited file 3
Combining two ideas? 3
Edit Macro script 2

Top