Could someone helps me?

I'm trying to write data on "TestData.txt", but place "TestData.txt" in the
same folder as my Excel program is placed.


Private Sub SaveThisSubInforGlobal()
Sheets("Response_2").Select

Open "TestData.txt" For Append As #1


Write #1, Cells(i, 1).Value; Cells(i, 2).Value; Cells(i, 3).Value

Close #1
End Sub


I find "TestData.txt" sometimes in the same folder as my Excel program is
placed, but also sometimes in "My Document." It is not consistent. Why is
this so? Is there any way I can place "TestData.txt" in the same folder as
my program is placed? I could do this easily with VB.6, but Excel VBA is
not consistent....


Thank you

Hi Takashi Yamauchi,

MsgBox Application.Path

but I don't consider it a good idea
to place data in the same location as the program's exe-file.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

Takashi Yamauchi wrote:
Is there any way I can place "TestData.txt" in the same folder as
my program is placed?

I don't know what is setting or varying the working directory in your
case. But here are some ideas so you can set it yourself right before
the write:

1. Assuming you want to set it to the directory of your .xls file you
can use ChDir Activeworkbook.path

2. If you really want to set it to the directory of the Excel
executable (excel.exe) you can use ChDir Application.path

If activeworkbook has not been saved, the curdir will be MyDocuments

Takashi Yamauchi wrote:
Could someone helps me?

I'm trying to write data on "TestData.txt", but place "TestData.txt" in the
same folder as my Excel program is placed.


Private Sub SaveThisSubInforGlobal()
Sheets("Response_2").Select

Open "TestData.txt" For Append As #1


Write #1, Cells(i, 1).Value; Cells(i, 2).Value; Cells(i, 3).Value

Close #1
End Sub


I find "TestData.txt" sometimes in the same folder as my Excel program is
placed, but also sometimes in "My Document." It is not consistent. Why is
this so? Is there any way I can place "TestData.txt" in the same folder as
my program is placed? I could do this easily with VB.6, but Excel VBA is
not consistent....


Thank you

wrote in message
...


Takashi Yamauchi wrote:
Is there any way I can place "TestData.txt" in the same folder as
my program is placed?

I don't know what is setting or varying the working directory in your
case. But here are some ideas so you can set it yourself right before
the write:

1. Assuming you want to set it to the directory of your .xls file you
can use ChDir Activeworkbook.path

Thank you. This worked, but only when I'm running the program from my main
hard disk. When I was running the program from a USB stick, the TestData.txt
is still placed in "My Document"....

But this is good enough.

Thank you again

takashi yamauchi


2. If you really want to set it to the directory of the Excel
executable (excel.exe) you can use ChDir Application.path