Problem opening text file...

[louishong]

Hello,


i have a simple macro that pulling in a text file:

Public Function Import_FND_GFM_BV(DataPathName, TemplateBook)

Application.StatusBar = "Importing fnd_gfm-BV.tsv..."
ChDir DataPathName

'Open BV.txt
Workbooks.OpenText Filename:="fnd_gfm-BV.tsv" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array
(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array
(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
DataWorkbook = ActiveWorkbook.Name

'Copy data
Range("B60000").Select
Selection.End(xlUp).Select
Range(Selection, "T2").Copy

'Paste data
Workbooks(TemplateBook).Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

Workbooks(DataWorkbook).Close

End Function

When I run it on drive C: (anywhere) it runs fine on any machine but
if I try to run the same Excel program on Drive D: or any network
drives I get a run-time error 1004...'fnd_gfm-BV.tsv' could not be
found. check the spelling of the file name, and verify that the file
location is correct.

When I check the DataPathName value in the immediate window the path
is correct and the file name is correct. This program seems to only
want to work from drive C:...it's got us stumped. Any suggestions
would be greatly appreciate it.


TIA

 

[Per Jessen]

Hi

I think you have to change the drive using the ChDrive statement before the
ChDir statement.

From the xl 2000 help file:
"The ChDir statement changes the default directory but not the default
drive. For example, if the default drive is C, the following statement
changes the default directory on drive D, but C remains the default drive:"

Hopes this helps.

 

[louishong]

Hi

I think you have to change the drive using the  ChDrive statement before the
ChDir statement.

From the xl 2000 help file:
"The ChDir statement changes the default directory but not the default
drive. For example, if the default drive is C, the following statement
changes the default directory on drive D, but C remains the default drive:"

Hopes this helps.

Thanks! I'll check into that.

 

[Dave Peterson]

You could drop the ChDir (and ChDrive) completely and just opent the file using
its full name/location:

Workbooks.OpenText Filename:="fnd_gfm-BV.tsv" _
becomes:
Workbooks.OpenText Filename:=datapathname & "\" & "fnd_gfm-BV.tsv" _

(does datapathname include the trailing backslash???)

 

[louishong]

You could drop the ChDir (and ChDrive) completely and just opent the fileusing
its full name/location:

    Workbooks.OpenText Filename:="fnd_gfm-BV.tsv" _
becomes:
    Workbooks.OpenText Filename:=datapathname & "\" & "fnd_gfm-BV.tsv" _

(does datapathname include the trailing backslash???)

In Excel 2003 it is pulling the correct drive letter so I don't think
that's the issue.

Dave, thanks for the shortcut tip...I do have a trailing "\"...i'll
give that a try.

 

[louishong]

You could drop the ChDir (and ChDrive) completely and just opent the fileusing
its full name/location:

    Workbooks.OpenText Filename:="fnd_gfm-BV.tsv" _
becomes:
    Workbooks.OpenText Filename:=datapathname & "\" & "fnd_gfm-BV.tsv" _

(does datapathname include the trailing backslash???)

Worked perfectly!