How to force current directory
 

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

How to force current directory
 

see if this helps you to incorporate the path
MsgBox ActiveWorkbook.Path
or
MsgBox CurDir
--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
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

How to force current directory
 

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

How to force current directory
 

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

How to force current directory
 

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

How to force current directory
 

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

How to force current directory
 

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

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

How to force current directory
 

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



"Tom Ogilvy" wrote in message
...
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