How to force current directory
Assuming the code is in the Masterfile:
spath = ThisWorkbook.Path
If Right(spath, 1) < "\" Then
spath = spath & "\"
End If
Workbooks.OpenText Filename:=spath & "q261pxda.", _
--
Regards,
Tom Ogilvy
"CLR" wrote in message
...
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" wrote:
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.", _
--
Regards,
Tom Ogilvy
"CLR" wrote in message
...
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" wrote:
sPath = CurDir
if Right(sPath,1) < "\" then
sPath = sPath & "\"
End if
Workbooks.OpenText Filename:=sPath & "q261pxda.*",
--
Regards,
Tom Ogilvy
"CLR" wrote in message
...
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
"CLR" wrote:
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
|