CODEWITHSUNDEEP
vbaexcel
A.Ellett
A.Ellett

Reputation: 373

Using VBA, how do I save a file in a relative directory

I have the following code to save the contents of an Excel Workbook as a tab delimited file.

Sub maketxtfile(className As String, rosterFileHandle As String)
Dim i As Long, gradebookContent As String
With Worksheets(className).UsedRange
    For i = 1 To .Rows.Count
        gradebookContent = gradebookContent & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
    Next
    End With

 Open Replace(ThisWorkbook.FullName, "Fall_2013_2014.xlsm", rosterFileHandle) For Output As #1
     Print #1, Mid$(gradebookContent, Len(vbCrLf) + 1)
Close #1

End Sub

The problem is that I don't want the tab delimited file to reside in the same directory as the xlsm file. I would like the file to reside in a subdirectory. I've seen solutions posted using absolute path names. That's not an option for me, I don't necessarily know what the path name will be in advance.

I thought I could do something like:

 Open Replace(ThisWorkbook.FullName, "Fall_2013_2014.xlsm", "rosters/" & rosterFileHandle) For Output As #1
     Print #1, Mid$(gradebookContent, Len(vbCrLf) + 1)
Close #1

But this got me an error. Though I'm working on a Mac, I tried using "rosters\" but while this worked, it did not place my file in the subdirectory, but in a file with \\ in its path name.

I would greatly appreciate a solution that will show me how to do this using relative path names.

Incidentally, I would not mind first creating the tab delimited file in the current directory and then moving it into a subdirectory.

Upvotes: 1

Views: 3802

Answers (3)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

Mac uses : as the path separator; it only looks weird if you're used to DOS/Windows. Or perhaps if you're a dyslexic *nix user ;-).

If by "my other machines" you mean Windows boxes, then no, it won't be portable, since the colon is restricted to delimiting drive letters in file names. Best bet is to do something like this:

Function PathSep() As String
#If Mac Then
    PathSep = ":"
#Else
    PathSep = "\"
#End If
End Function

Then you could:

Open Replace(ThisWorkbook.FullName, "Fall_2013_2014.xlsm", "." & PathSep & "rosters" & PathSep & rosterFileHandle) For Output As #1

Upvotes: 1

Graham Anderson
Graham Anderson

Reputation: 1239

This should do the trick 

ThisWorkbook.SaveAs (ThisWorkbook.Path & "\Rosters\" & ThisWorkbook.Name)

Edit:

Changed code to save the text file in stead also used chr(92) to reporesent the path seperator.

Sub maketxtfile()
Dim i As Long, gradebookContent As String
Dim rosterFileHandle As String

rosterFileHandle = "tabtest.txt"
rosterFileHandle = ThisWorkbook.Path & Chr(92) & "Rosters" & Chr(92) & rosterFileHandle

With ActiveSheet.UsedRange
    For i = 1 To 10
        gradebookContent = gradebookContent & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
    Next
    End With
Open (rosterFileHandle) For Output As #1
     Print #1, Mid$(gradebookContent, Len(vbCrLf) + 1)
Close #1

End Sub

Upvotes: 0

A.Ellett
A.Ellett

Reputation: 373

After much searching, I found something that works.

I can write:

Open Replace(ThisWorkbook.FullName, "Fall_2013_2014.xlsm", ".:rosters:" & rosterFileHandle) For Output As #1
     Print #1, Mid$(gradebookContent, Len(vbCrLf) + 1)
Close #1

The syntax seems a bit weird using ":" to indicate the directory path, but it works. Now the question is whether this is portable and will work correctly on my other machines.

Upvotes: 1

Related Questions