Help needed with Changing Directories to Import a Text File

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
Please what is wrong with this code? Sometimes it works and sometimes (most of the time) it does not. What am I missing.

I want to open a text file that is store on the R Dirve in a folder called New Data. There are several files in this folder, each month a new file with a new name will be added, so I what the user to select the correct file from the New Data folder

Thanks
L.

Dim myfile
ChDrive "R"
ChDir "R:\month\December 2005\New Data\ *.txt"

myfile = Application.GetOpenFilename(filefilter:="Text Files (*.txt),.txt", MultiSelect:=True)

For x = LBound(myfile) To UBound(myfile)

Workbooks.OpenText Filename:=myfile(x), Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2)

Next
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Re: Help needed with Changing Directories to Import a Text F

Liz_I3 said:
...Sometimes it works and sometimes (most of the time) it does not. ...

What is happening when it doesn't work?
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
HI Barry
Nothing, It just moves on the the next macro.

It seem to read as far as the Chdir, then does not read the Myfile, skips down to the end sub.

L
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Try
Code:
Dim myfile
Dim x As Long
ChDrive "G"
ChDir "G:\"

myfile = Application.GetOpenFilename(filefilter:="Text Files (*.txt),.txt", MultiSelect:=True)

For x = LBound(myfile) To UBound(myfile)
    Workbooks.OpenText Filename:=myfile(x), Origin:=xlWindows _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 2)
Next x

Changed Next to Next x
 

Watch MrExcel Video

Forum statistics

Threads
1,118,287
Messages
5,571,318
Members
412,382
Latest member
Langtn02
Top