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

Question

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.

Duplicate to http://stackoverflow.com/questions/7198871 – Wolfgang Kuehn Aug 25 '13 at 17:47 — Wolfgang Kuehn, Aug 25 '13 at 17:47

score 1 · Answer 1 · answered Aug 25 '13 at 17:00

1

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.

answered Aug 25 '13 at 17:00

A.Ellett

331
2
10

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 ;-) – Steve Rindsberg Aug 26 '13 at 14:35

score 1 · Accepted Answer · answered Aug 26 '13 at 14:39

1

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

answered Aug 26 '13 at 14:39

Steve Rindsberg

14,442
1
29
34

Thanks for the point about portability. Very true. But the other machines are indeed all Macs. My comment about `:` being weird is because `/Users//directory/path/name` how my path names are identified on my Mac. – A.Ellett Aug 26 '13 at 17:20
" is because /Users//directory/path/name how my path names are identified on my Mac" ... that sentence no verb. ;-) Mac has used : as path sep since it had paths to separate, but since OS X, it's been Unix under the hood, so for some things the / unix path separator works, for some not. In that sense, the / is weird/:\johnny-come-lately. At least to the Mac. – Steve Rindsberg Aug 27 '13 at 00:47
Thank you for the FYI about `:`. I was not aware of the history there. Also, thank you for pointing out my verbless sentence. ;) – A.Ellett Aug 27 '13 at 01:52
Why the # sign ... in this? "...For Output As #1" That's copied directly from your original code. IAC, it's a peculiar bit of VB syntax; I've never quite understood why VB requires the # in some cases and not others, but that's for a different discussion thread. – Steve Rindsberg Aug 27 '13 at 14:17

Graham Anderson · Answer 3 · 2013-08-25T17:22:17.797

0

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

edited Aug 25 '13 at 17:22

answered Aug 25 '13 at 16:34

Graham Anderson

1,209
10
17

Is that a relative path name??? Also, it's not the workbook that I want to move. It's the tab delimited file that I want to move. – A.Ellett Aug 25 '13 at 16:37
This doesn't save the file in a subdirectory. It just creates a file with the name "\\Rosters\\roster.txt" in the current directory – A.Ellett Aug 25 '13 at 16:41
Is rosterfilehandle the name of the file? – Graham Anderson Aug 25 '13 at 16:47
Yes, it's a string variable that holds the name of the file. But even if I passed the value "./rosters/roster.txt" through the variable, I still get an error. – A.Ellett Aug 25 '13 at 16:48
What error? File/Path not found? Make a function to replace Path Separator! Use `Application.PathSeparator` instead of predefined character. – PatricK Aug 26 '13 at 03:31

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

3 Answers3