Help needed with Changing Directories to Import a Text File

[Liz_I3]

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

 

[Barrie Davidson]

Re: Help needed with Changing Directories to Import a Text F

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

What is happening when it doesn't work?

 

[Liz_I3]

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]

Try

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

 

[Liz_I3]

Thanks Barrie
It is now working every time
Thank you
L