2

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.

Community
  • 1
  • 1
Dov Miller
  • 1,958
  • 5
  • 34
  • 46

1 Answers1

3

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
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115