Hi I have a problem with Excel that I would like to use a Macro to do.
I am unfamilar with the syntax used in Excel so this task is proving
to be somewhat tricky.

I would like to copy all entries from one colum into a text file.
Additionally, I would like to create a series of sub directories in
the location that the file is saved that correspond to the columns
extracted via the text file.

How would I go about writng a Macro to perform such a task? This is as
far as I have got:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
MkDir PathForTextFile & "\" & CellValue
WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

I have tried removing the "WholeLine = WholeLine & "\" & myCell.Text &
vbNewLine " line, but without success.

The code below creates a text file from a column of data.
Not sure what you mean about the MkDir part though ? You mean you want a
directory structure nested to a level of 99 folders ?
I would bet you reach the Windows limit of MAX_PATH (260 characters) before
you achieve that.
If not, then explain that part again.

Sub ExportToPRN()
Dim FName As Variant
Dim FNum As Integer
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

If FName = False Then Exit Sub

FNum = FreeFile
Open FName For Output Access Write As #FNum
Print #FNum, Join(Application.Transpose(Range("A1:A20")), vbNewLine)
Close #FNum

End Sub

NickHK

wrote in message
ups.com...
Hi I have a problem with Excel that I would like to use a Macro to do.
I am unfamilar with the syntax used in Excel so this task is proving
to be somewhat tricky.

I would like to copy all entries from one colum into a text file.
Additionally, I would like to create a series of sub directories in
the location that the file is saved that correspond to the columns
extracted via the text file.

How would I go about writng a Macro to perform such a task? This is as
far as I have got:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
MkDir PathForTextFile & "\" & CellValue
WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

I have tried removing the "WholeLine = WholeLine & "\" & myCell.Text &
vbNewLine " line, but without success.

Lets say I have a column with three values:

SomeValue1
SomeValue2
SomeValue3

I want the macro to not only generate a text document but also to
create three sub directories within a speicfied root directory.

So let's say I want to specify to save the text file in "C:\Temp",
then inside temp, I would have the following directories:

C:\Temp\SomeValue1
C:\Temp\SomeValue2
C:\Temp\SomeValue3

Tha's what I am after.

You can add this to the previous code. You should add error trapping in case
the dir already exist, name invalid etc

Dim Cell As Range
Dim OutputDir As String

OutputDir = Left(FName, InStrRev(FName, "\"))

For Each Cell In Range("A1:A20")
MkDir OutputDir & Cell.Value
Next

NickHK

wrote in message
oups.com...
Lets say I have a column with three values:

SomeValue1
SomeValue2
SomeValue3

I want the macro to not only generate a text document but also to
create three sub directories within a speicfied root directory.

So let's say I want to specify to save the text file in "C:\Temp",
then inside temp, I would have the following directories:

C:\Temp\SomeValue1
C:\Temp\SomeValue2
C:\Temp\SomeValue3

Tha's what I am after.

Woudl it be possible to write the whole Macro with that functionality
in it please? I have tried this:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String
Dim Cell As Range
Dim OutputDir As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C2")
WholeLine = ""

For Each myCell In myRange

WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
MkDir OutputDir & Cell.Value

Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

But have had no success. What am I doing wrong?

It may help if you even looked at the code I have posted.

NickHK

wrote in message
oups.com...
Woudl it be possible to write the whole Macro with that functionality
in it please? I have tried this:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String
Dim Cell As Range
Dim OutputDir As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C2")
WholeLine = ""

For Each myCell In myRange

WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
MkDir OutputDir & Cell.Value

Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

But have had no success. What am I doing wrong?