How to set the location of a file created by a macro

Question

I am converting an excel file to a text file by a macro and I want the text file's location to be the same folder as the excel worksheet's location.

My code is:

Dim strPath As String
strPath = "MyFileName.dat"
Dim fnum As Integer
fnum = FreeFile()
Open strPath For Output As #fnum

'my code

Close #fnum

When running it always goes to Documents. I tried "../MyFileName.dat" and it worked with some of the locations I tried putting the excel worksheet in but not with all.

What's the right way to do this. Thank you.

Answer 1

Assuming the workbook in question is the ActiveWorkbook, this will work. It get the workbook's full path with FullName and subsitutes the data file's name for for the workbook's:

Sub test()
Dim wb As Excel.Workbook
Dim strPath As String

Set wb = ActiveWorkbook
strPath = Replace(wb.FullName, wb.Name, "MyFileName.dat")
Dim fnum As Integer
fnum = FreeFile()
Open strPath For Output As #fnum

'my code

Close #fnum
End Sub