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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>