Sunday, 25 August 2013

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

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.

No comments:

Post a Comment