Setting directory for file save in macro

[Jake]

Hi

Thanks to a generous poster I now have a great bit of code to use in a macro
for Excel which saves a text file (I'm no VBA programmer so this was really
helpful), i.e.:
Open "Test.txt" For Output As #1
(rest of code)

My problem now is this:

The macro saves test.txt to Excel's current active folder, rather than the
folder that the current workbook is contained in.

For example, if I last saved an Excel workbook to c:\workbooks\, and the
workbook that I have open exists in c:\workbooks\workbook1\, when I run the
macro it saves the text file in c:\workbooks\. If I save over my current
workbook (File -> Save As, then overwrite the workbook) it would seem that
Excel's working folder changes to c:\workbooks\workbook1, and that's where
the macro now saves text.txt.

How do I make it so that the macro saves text.txt in the same folder as the
workbook that contains it?

Thanks in advance

Jake

 

[JE McGimpsey]

One way:

Dim fPath As String
fPath = ThisWorkbook.Path & Application.Separator
Open fPath & "Text.txt" For Output As #1

 

[Frank Kabel]

Hi Jake
try
Sub foo()
Dim fileSaveName
ChDrive "C:"
ChDir "C:\workbooks"
ActiveWorkbook.SaveAs Filename:="Test.xls"
End If
end sub

 

[Nikos Yannacopoulos]

Jake,

substitute that line of code with:

pth = ActiveWorkbook.Path
Open pth & "\Test.txt" For Output As #1

The first line will assign variable pth the path of the current workbook,
and the second one will now precede the filename with the path (plus the
required backslash between folder and file name).

HTH,
Nikos

 

[Jake]

Thanks for the suggestion - I tried it and got this:

Run-time error '438'
Object doesn't support this property or method

That's at:
fpath = ThisWorkbook.Path & Application.Separator

Any idea what's happening there?

TIA

Jake

 

[Jake]

I ended up using:

Dim fPath As String
fPath = ActiveWorkbook.Path
Open fPath & "\test.txt" For Output As #1

which worked a treat

Many thanks again for the help.

Jake

 

[Jake]

Thanks for the help - that worked very well.

I was trying to do that last night, but I was using ThisWorkbook instead of
ActiveWorkbook.

Jake

 

[JE McGimpsey]

Should have been Application.PathSeparator