Opening a file from any active directory

The file that this code is located in is often sent to others to use which means I have to modify the directory and path each time I send this file in order for the VB code to work.

Workbooks.OpenText Filename:= _
        "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current\C37.txt"

Is there a way to have the file opened from any active directory it happens to reside in?

Thanks for any help on this.

Re: Opening a file from any active directory

Hi Tony,

I moved the excel file along with the text file into another directory and tried the code but got an error message. Is there something else I need in the excel workbook?

Thanks for your help on this.

Re: Opening a file from any active directory

Here's what I had:

ChDir "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current"
    Workbooks.OpenText Filename:= _
        "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current\C37.txt", _
        Origin:=437, StartRow:=39, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(12, 1), Array(16, 1), Array(28, 1), Array(37, 1), Array(43, 1), Array(48 _
        , 1), Array(75, 1), Array(82, 1), Array(88, 1), Array(96, 1), Array(102, 1), Array(110, 1), _
        Array(128, 1), Array(130, 1), Array(150, 1), Array(156, 1), Array(161, 1), Array(190, 1)) _
        , TrailingMinusNumbers:=True

When I replaced the code, this is how it was set up:

Workbook.OpenText Filename:=ThisWorkbook.Path & "\c37.txt", _ 
        Origin:=437, StartRow:=39, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(12, 1), Array(16, 1), Array(28, 1), Array(37, 1), Array(43, 1), Array(48 _
        , 1), Array(75, 1), Array(82, 1), Array(88, 1), Array(96, 1), Array(102, 1), Array(110, 1), _
        Array(128, 1), Array(130, 1), Array(150, 1), Array(156, 1), Array(161, 1), Array(190, 1)) _
        , TrailingMinusNumbers:=True

Re: Opening a file from any active directory

Run time error '424': Object required

Re: Opening a file from any active directory

Hi Tony,

Sorry it took so long to get back on this, I was out of town for a while.

I tried the following code in a blank workbook but still got the 424 runtime error code. Both the text file and the excel workbook are residing on my desktop.

Here's what I had:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/7/2006 by keith
'
Workbook.OpenText Filename:=ThisWorkbook.Path & "/p51.txt"
'
End Sub

Thanks for any help on this.

Re: Opening a file from any active directory

Hi Tony,

It was a saved file. Both the excel file and the text file are on my desktop.

Keith

Re: Opening a file from any active directory

Yes, both files are in the same directory. I've tried moving them into different directories all with the same result, error message 424 'object required'.