How to force current directory

[Guest]

Hi All.......

I am using the following code to open a non-excel file so I can manipulate
and extract the data from it I need. I would like to force Excel to look in
the same directory that the file I'm using to open the non-excel file came
from........rather than some directory that some default figures I should
use........problem is, the file "is not found" in that default directory.
Note, the actual file has no extention.


Workbooks.OpenText Filename:="q261pxda.*", _
Origin:=xlWindows, 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))

Any help would be much appreciated,
TIA
Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

see if this helps you to incorporate the path
MsgBox ActiveWorkbook.Path
or
MsgBox CurDir

 

[Guest]

Hi Don......

Thanks for the response, but I can't figure out what to do with those
phrases. When I just add them to my macro, they only pop-up a window telling
me the path, or Cur Dir.........what I'm really looking for is a line of code
to force Excel to goto the Current Directory to find the file I'm telling it
to open, whatever that Current Directory may be, and overriding the "Default
file Location:" setting in Excel's options........I dunno how clear that is,
it reads funny.......it may not even be possible.

Chuck

 

[Tom Ogilvy]

sPath = CurDir
if Right(sPath,1) <> "\" then
sPath = sPath & "\"
End if

Workbooks.OpenText Filename:=sPath & "q261pxda.*",

 

[Guest]

Hi Tom.......

Thanks for the response but I still can't get it working.....here's where I
put your code, but it still keeps looking to the default directory as set in
Tools > Options......what did I do wrong?

'Open the new q261 file from IS
'this first part intends to set up the Current Directory as default for the
Open
spath = CurDir
If Right(spath, 1) <> "\" Then
spath = spath & "\"
End If

Workbooks.OpenText Filename:=spath & "q261pxda.", _
Origin:=xlWindows, 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))


thanks again,
Chuck

 

[Tom Ogilvy]

That is the way it would be set up.

If it is not going to the correct directory then the correct directory must
not be the current directory.

You need to query Curdir to see what it is returning.


spath = CurDir
If Right(spath, 1) <> "\" Then
spath = spath & "\"
End If
ans = msgbox("Is this correct" & vbNewLine & _
sPath & "q261pxda." , vbYesNo)
if ans = vbNo then exit sub
Workbooks.OpenText Filename:=spath & "q261pxda.", _






If Curdir is not the correct string, then what property will give you the
correct string?

If you know the correct path then (assume it is "C:\Myfolder\MyFiles\" )

then you could do

sPath = "C:\Myfolder\MyFiles\"
Workbooks.OpenText Filename:=spath & "q261pxda.", _

 

[Guest]

Hi Tom......

My Excel is set through Tools > Options to have the "Default File Loction:"
as C\My Documents. If I start my MasterFile by going into WindowsExplorer
and double-clicking on my MasterFile name, then when I run the macro, it
fails to find the q261pxda.* file because it looks in the c:\My Documents
directory. If I start Excel and then go open my MasterFile, then the macro
works and it finds the q261pxda.* file in the same directory that I opened my
MasterFile from.........that is why I wanted the VBA to determine where my
MasterFile opens from and look there as the first directory to find
q261pxda.*, because others will use the MasterFile and I will not know how
their Excel's are set........I don't want to hard-code the location in,
because it will change on their machines, but will always be the same
directory that the MasterFile is in.........hope that makes sense.

Chuck

 

[Tom Ogilvy]

Assuming the code is in the Masterfile:

spath = ThisWorkbook.Path
If Right(spath, 1) <> "\" Then
spath = spath & "\"
End If
Workbooks.OpenText Filename:=spath & "q261pxda.", _

 

[CLR]

How unbelieveably simple .....(when one knows how)...........

Thank you very very much Tom, your solution to my problem works perfectly.
I appreciate your time and patience.

Vaya con Dios,
Chuck, CABGx3